locked
Help with complex query using TableAdapter. RRS feed

  • Question

  • I have Product and ProductData tables that are related by a ProductId. I also have Stock and IncomingStock tables that hold product Ids and quatities of the products in stock. Both tables have an InventoryID field that identifies at what time was the quantity of the particular product recorded.

    I want to be able to get a table containing the variance between two inventories - it will have the beginning quantity for each product, the incoming quantity and the end quantity. 

    Using the QueryBuilder I was unable to generate a query that works, so I wrote something by hand that I tested in Management Studio and it works. There are 3 problems:

    1. When I paste it in the TableAdapter's fill method, it complains that the query is too big

    2. Just for testing I pasted "half" of the query (the part that gets the opening inventory and incoming quantities numbers) - it ran fine but it was much slower than running in Management Studio (db is on the local machine).

    3. Opening the query from step 2 in the designer caused the query to change - the query designer wants to use a cross join for some reason - unfortunately the result from the cross join does not result in the correct set of rows being returned. This might be a bug with query builder.

     

    Here is what I'm doing (simplified version):

     

    Select SUM(Stock.ProductQuantity) as ProdQuantity, SUM(IncomingStock.ProductIncomingQuantity) as IncomingQuantity, 
    Inventory.InventoryId, ProductData.ProductID FROM ProductData, Inventory, Stock FULL OUTER JOIN IncomingStock ON Stock.InventoryID = IncomingStock.InventoryId and
    Stock.ProductID = IncomingStock.ProductId WHERE ((ProductData.ID = Stock.ProductVarietyID OR ProductData.ProductID = IncomingStock.ProductVarietyID) AND (Inventory.InventoryID = Stock.InventoryID OR Inventory.InventoryID = IncomingStock.InventoryID)) GROUP BY ProductData.ProductID, Inventory.InventoryID HAVING (Stock.InventoryID = @openingInventory) OR (IncomingStock.InventoryID = @openingInventory) ORDER BY ProductID;

    I'm doing SUMs because the same Product could be in the inventory more than once. Because the full outer join returns NULLs for the rows that are not matching, I want to have a single column holding the "merged" inventory and Product ids, so I select the corresponding fields from the ProductData and Inventory tables.


    Opening the above query in the query designer results in the following:

     

    Select SUM(Stock.ProductQuantity) as ProdQuantity, SUM(IncomingStock.ProductIncomingQuantity) as IncomingQuantity, 
    Inventory.InventoryId, ProductData.ProductID FROM Inventory CROSS JOIN ProductData CROSS JOIN Stock FULL OUTER JOIN IncomingStock ON Stock.InventoryID = IncomingStock.InventoryID AND Stock.ProductId= IncomingStock.ProductID WHERE (ProductData.ProductID = Stock.ProductID OR ProductData.ProductID = IncomingStock.ProductID) AND (Inventory.InventoryID = Stock.InventoryID OR Inventory.InventoryID = IncomingStock.InventoryID) GROUP BY ProductData.ProductID, Inventory.InventoryID HAVING (Stock.InventoryID = @openingInventory) OR (StockTotals.IncomingStock= @openingInventory) ORDER BY ProductData.ProductID

     

    Executing the query generated by Query Builder is either very slow or it times out. The result sets does not include all data expected.

     

    The bigger problem is that this only gives me the opening inventory and the incoming quantities. To get the closing numbers, I insert the result from the previous query into a temp table, and then in a similar query I use a full outer join to join the temp table and the Stock table again. This time I'm passing in the closing inventory id. That works fine but it's too big for the tableAdapter.

    Any ideas how to write this? It's going to be used to power a report.

     

     


    Monday, June 20, 2011 4:50 AM

Answers

  • Hi Wentsislav,

    Thanks for your clarification, my bad.

    I think you shouldn't use Query Builder, TableAdapter Configuration Wizard is used to determine what data should be loaded into the table. There are two ways to complete this task, one is just type your SQL statement, the other is to use the Query Builder to construct it to generate T-SQL. You can just past your T-sql and click Finish.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Alan_chen Friday, July 8, 2011 10:06 AM
    Friday, June 24, 2011 2:09 AM

All replies

  • Anyone?
    Monday, June 20, 2011 10:28 PM
  • Hi Ventsislav,

    Welcome!

    According to your description, I find the a know issue here: http://connect.microsoft.com/VisualStudio/feedback/details/574219/tableadapter-configuration-wizard

    I think you can use ADO.NET to run you query or set dataadapter's command instead of using TableAdapter configuration Wizard.

    http://support.microsoft.com/kb/308507

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, June 21, 2011 6:52 AM
  • That actually has nothing to do with the issue I'm having. The problem is that when I paste the SQL query in the tableAdapter wizard and then click "Query builder" the query builder is not able to interpret the query correctly and ends up generating a cross join query - which returns a different result set.
    Thursday, June 23, 2011 5:49 PM
  • Hi Wentsislav,

    Thanks for your clarification, my bad.

    I think you shouldn't use Query Builder, TableAdapter Configuration Wizard is used to determine what data should be loaded into the table. There are two ways to complete this task, one is just type your SQL statement, the other is to use the Query Builder to construct it to generate T-SQL. You can just past your T-sql and click Finish.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Alan_chen Friday, July 8, 2011 10:06 AM
    Friday, June 24, 2011 2:09 AM
  • Hi Wentsislav,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, July 5, 2011 9:33 AM