Nested Common Table Expressions

If you’ve looked up a Common Table Expressions in Books Online, you might have noticed that Microsoft says that you cannot have nested CTEs (meaning, you can’t define a CTE in the definition of another CTE. Although this is technically true, you can still accomplish the same functionality in a different manner.

Here is an example of a simple CTE using the AdventureWorks database that will return a list of orders.

USE AdventureWorks

;WITH CTE_Orders (OrderID, OrderDate) AS
(
  SELECT PurchaseOrderID, OrderDate
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT * FROM CTE_Orders

OrderID     OrderDate
----------- ----------
1           2001-05-17
2           2001-05-17
3           2001-05-17
4           2001-05-17
5           2001-05-31
...

What isn’t very clear is the use of multiple CTE definitions in one statement. Here is an example of multiple CTEs used in one statement. Each CTE in the statement has its own definition, and the final SELECT statement is able to query both CTEs. This will return a list of dates that orders were made along with the number of products ordered and the total amount of those products for each date.

;WITH CTE_OrdersDetail (OrderID, OrderDetailQty, OrderDetailAmt) AS
(
  SELECT PurchaseOrderID, OrderQty, LineTotal
    FROM Purchasing.PurchaseOrderDetail
),

CTE_Orders (OrderID, OrderDate) AS
(
  SELECT PurchaseOrderID, OrderDate
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT o.OrderDate, SUM(od.OrderDetailQty) AS TotalOrderQty, SUM(od.OrderDetailAmt) AS TotalOrderAmt
  FROM CTE_Orders o
    INNER JOIN CTE_OrdersDetail od ON o.OrderID = od.OrderID
  GROUP BY o.OrderDate
  ORDER BY o.OrderDate

OrderDate  TotalOrderQty TotalOrderAmt
---------- ------------- -------------
2001-05-17 563           9491.518
2001-05-31 2765          94404.303
2002-01-14 718           39874.7685
2002-01-15 11931         259365.2145
2002-02-08 2768          50781.36
...

If you take this concept of having multiple CTE definitions in one statement, you can then apply it to a statement with nested CTEs. I’m using the following statement to return the same result set as in the previous example, but this time I want to know the number of orders for each date that had more than 100 products ordered.

;WITH CTE_OrdersDetail (OrderID, OrderDetailQty, OrderDetailAmt) AS
(
  SELECT PurchaseOrderID, OrderQty, LineTotal
    FROM Purchasing.PurchaseOrderDetail
),

CTE_Orders (OrderID, OrderDate, IsLargeOrder) AS
(
  SELECT PurchaseOrderID, OrderDate
       , (
         SELECT CASE
                  WHEN SUM(OrderDetailQty) >= 100 THEN 1
                  ELSE 0
                END
           FROM CTE_OrdersDetail d
           WHERE h.PurchaseOrderID = d.OrderID
         )
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT o.OrderDate, SUM(od.OrderDetailQty) AS TotalOrderQty, SUM(od.OrderDetailAmt) AS TotalOrderAmt, SUM(o.IsLargeOrder) AS NumLargeOrders
  FROM CTE_Orders o
    INNER JOIN CTE_OrdersDetail od ON o.OrderID = od.OrderID
  GROUP BY o.OrderDate
  ORDER BY o.OrderDate

OrderDate  TotalOrderQty TotalOrderAmt         NumLargeOrders
---------- ------------- --------------------- --------------
2001-05-17 563           9491.518              1
2001-05-31 2765          94404.303             5
2002-01-14 718           39874.7685            1
2002-01-15 11931         259365.2145           26
2002-02-08 2768          50781.36              5
...

As you can see from this example, the CTE_OrdersDetail expression is being referenced within the definition of the CTE_Orders expression, giving you the functionality of nested CTEs. In order for a statement like this to work properly, you must declare each CTE definition before it is used in another CTE. This means that since CTE_OrdersDetail is being reference within CTE_Orders, I must declare CTE_OrdersDetail first. Otherwise, if the two definitions were switched, SQL would not be able to find the CTE_OrdersDetail object when it goes to verify the CTE_Orders expression.

About these ads