[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored Procedure Using Nested Queries

Posted on 2004-11-22
3
Medium Priority
?
2,906 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:CCorrea01
3 Comments
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12645760
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
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 2000 total points
ID: 12646401
Either your code was copied in poorly, or I think you are mixing the two types of subquery/derived tables.

The position of your subquery indicates that you want a true subquery.  However, a true subquery can return only one value per row, and this is returning two values per row.  To get two values per row, you would need a Derived Table.

This appears that it can be done in either method, e.g.
(Subquery)

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

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
Where dbo.ProjectMaster.StoreNumber = 9412


(Derived Table)

Select dbo.ProjectDemand.ProjectNumber, dbo.ProjectMaster.StoreNumber, dbo.ProjectDemand.SKUNumber,
     dbo.SKUMaster.SKUDescription As ItemDescription, dbo.ProjectDemand.Quantity As QtyDrawn,
     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
         (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 ON dbo.ProjectDemand.SKUNumber =  OrderSummary.SKUNumber
Where dbo.ProjectMaster.StoreNumber = 9412


The important questions to ask, though:

(1) is there an index on SKU in OrderDetails?  On StoreNumber in OrderHeaders?

(2) Will you be doing this on a store-by-store basis, or on a mass basis, or both?  The two methods can have different efficiency results depending on how many individual locations are being queries.

(3) How much data are you manipulating at once.  Once often overlooked fact of working with SQL Server (or almost any DB) is that once processing a query involves involuntary swapping to disk, processing speed decreases dramatically.  So, even if a mass query is intrinsically faster, it can be faster in reality to segment your query and use temp tables &c.

Based on your description of actions, I would first try to create two or three views and make a query that joins the views based on indexed fields (add indexes if needed).  One view for the Order information, one for the Shipment information, and then either a view or a base query for the BoM/Project Demand.
0
 

Author Comment

by:CCorrea01
ID: 12650688
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
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question