SQL Query Fundamentals (Party 3) - Nested Queries

You may notice from time to time, you need to sum or count the rows in a query, but you can't do so because you have to include a number of other fields in the query, which you don't want to include in your group by.  

If you don't know what I am talking about, if you want to sum or count the rows of a field in a query, you must add a Group By statement to a Query.

When you need to perform this action, you use a nested query.  A nested query is a query within a query, like a little Russian Nesting Doll.  Nested queries can be simple or complex.  The purpose of this post is to present a simple example. I will do my best to put forth another post, down the road, to highlight a complex one.

The new concepts covered in this query are as follows:

Sum a field
Group By a field
NoLock Hints on tables

In addition, this query makes use of several other elements covered in past posts, so if you would like to observe another usage of a previously discussed topic you will find the following:

Aliases
Cases
Conversions
Inner Joins
Left Joins
Comments

Here is a sample query

You will note from the comments at the beginning, the purpose of this query is to determine the status of manufacturing receipts for Make to Order Work Orders, which are linked to Sales Orders in Dynamics GP.  

In order to total (sum) Work Order receipts from the MOP1100 table, we used a nested query to Select the SUM of the Quantity Received field when the records are grouped by MANUFACTUREORDER_I field or MO#.  So to create a nested query, you simple write your query within Parenthesis and Alias the query after you close the Parenthesis - in this case we used RCT as an alias. 

We then left joined the RCT table (nested query) to the MOSO (MOP/SOP) Link table on RCT.MANUFACTUREORDER_I = MOSO.MANUFACTUREORDER_I fields. This makes it possible to reference the sum of Quantity Received in the MOP1100 table for any work order linked to a Sales Order in the MOP/SOP Link table (IS010001).

This query also introduces an entirely new concept of the NoLock hints.

We have gone round and round internally discussing the use of NoLock hints, which can and do produce "Dirty Reads" by returning data that is not yet committed in SQL, but the PROS in most cases outweigh the cons:

PROS - Speed with which queries return values and the inclusion of what is provisional data in your results - this provisional data is likely to be committed in a ridiculous number of instances and will be abandoned in very few.

CONS - Provisional Data will not always be committed.

NoLock SQL Hint Explained