asked on

Stored Procedure Using Nested Queries

Hi,

I’m trying to create a stored procedure that is based off of tables that track Bill of Materials (Project Demand), Orders (Headers and Details) and Shipments (Headers and Details). What I’m trying to do is return a record set that show all SKU & Description and the quantity drawn, and then shows any summed quantities that have been ordered or shipped. I initially created this as separate views that I then linked by Store Number and SKU, but it was running very slowly. What I’m trying to do now is create a store procedure that limits each of the record sets by store, and then joins them by SKU, but I’m having trouble with nested query syntax.

The questions I have are, is this the right approach, and if so, how do I link the sub-queries by SKU number?

Here’s is what I have so far. It doesn’t include the shipment tables yet, since I’m still trying to get the orders portion correct. Also, a store number variable will replace the actual store number value I’m using in the example. Any help would be appreciated!

Thanks,

Correa

----------------------------------

Select dbo.ProjectDemand.ProjectNumber, dbo.ProjectMaster.StoreNumber, dbo.ProjectDemand.SKUNumber,
      dbo.SKUMaster.SKUDescription As ItemDescription, dbo.ProjectDemand.Quantity As QtyDrawn,
      (Select dbo.OrderDetails.SKUNumber, Sum(dbo.OrderDetails.QtyOrdered) As QtyOrdered
      From dbo.OrderDetails Inner Join
dbo.OrderHeaders ON dbo.OrderDetails.OrderID = dbo.OrderHeaders.OrderID
      Where (dbo.OrderHeaders.StoreNumber = 9412)
      Group By dbo.OrderDetails.SKUNumber
      )OrderSummary

From
      dbo.ProjectDemand Inner Join dbo.ProjectMaster On dbo.ProjectDemand.ProjectNumber = dbo.ProjectMaster.ProjectNumber
      Left Outer Join dbo.SKUMaster On dbo.ProjectDemand.SKUNumber = dbo.SKUMaster.SKUNumber
      Left Outer Join dbo.ProjectDemand On dbo.ProjectDemand.SKUNumber = OrderSummary.SKUNumber
Where dbo.ProjectMaster.StoreNumber = 9412

Duane Lawrence

Select dbo.ProjectDemand.ProjectNumber, dbo.ProjectMaster.StoreNumber, dbo.ProjectDemand.SKUNumber,
dbo.SKUMaster.SKUDescription As ItemDescription, dbo.ProjectDemand.Quantity As QtyDrawn,
(Select dbo.OrderDetails.SKUNumber, Sum(dbo.OrderDetails.QtyOrdered) As QtyOrdered
From dbo.OrderDetails Inner Join
dbo.OrderHeaders ON dbo.OrderDetails.OrderID = dbo.OrderHeaders.OrderID
Where (dbo.OrderHeaders.StoreNumber = 9412)
Group By dbo.OrderDetails.SKUNumber )

From
dbo.ProjectDemand Inner Join dbo.ProjectMaster On dbo.ProjectDemand.ProjectNumber = dbo.ProjectMaster.ProjectNumber
Left Outer Join dbo.SKUMaster On dbo.ProjectDemand.SKUNumber = dbo.SKUMaster.SKUNumber
Left Outer Join dbo.ProjectDemand as pd On dbo.pd.SKUNumber = OrderSummary.SKUNumber
Where dbo.ProjectMaster.StoreNumber = 9412

Duane

ASKER CERTIFIED SOLUTION

Brendt Hess

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CCorrea01

ASKER

Truth be told, I had no clue what I was really doing.

The first example is exactly what I was trying to do, and it works like a charm! To answer the questions you asked:

1.      Yes, on both fields.

2.      This will be on a store-by-store basis, and will be the record set for an Inquiry form in Access.

3.      Each store will return about 300 records via the project demand table, and whatever match on both orders and shipments (not included in this example, but based on what you showed me here, I was able to add them easily.

My initial attempt did use three separate that were joined by a query, and that was what took so long to run per store. After I broke them apart and ran them individually for a specific store is when I saw some acceptable performance. That’s what prompted me to move in this direction.

Again, thanks for the help on this. I should be able to apply this moving forward, as I have a couple other inquiry forms that will be looking at this data, but from an inverse perspective (like Stores using a specific SKU).

-Correa