Anthony Trudeau

Using CTEs in SQL Server 2005

One of the uses mentioned for common table expressions (CTE) in SQL Server 2005 is recursive queries.  A CTE allows you to define a virtual view that can be used in another statement.  At first glance I thought this was awesome.  In fact I had just recently implemented a user-defined function to calculate the “pieces per” based on a global trade item number (GTIN), because recursion is necessary to calculate the value.

The user-defined function that I created is as follows:

CREATE FUNCTION RFID_GetPiecesPer(@gtin nvarchar(14), @item nvarchar(30))
RETURNS decimal(6,0)
AS
BEGIN
     DECLARE @piecesPer decimal(6,0)
     DECLARE @quantity decimal(6,0)
     DECLARE @currentGtin nvarchar(14)
     DECLARE @currentItem nvarchar(30)

     SET @piecesPer = 1
     SET @quantity = 0
     SET @currentGtin = @gtin
     SET @currentItem = @item

     WHILE (@currentGtin IS NOT NULL AND @currentItem = @item)
     BEGIN
          SELECT @currentItem = G.EnterpriseItem, @quantity = C.QuantityOfNextLowerLevelTradeItem,
          @currentGtin = C.TradeItemIdentificationOfNextLowerLevelTradeItem
          FROM GD_ChildTradeItem C
          RIGHT OUTER JOIN GD_GlobalTradeItem G ON C.GlobalTradeItemKey = G.GlobalTradeItemKey
          WHERE G.GlobalTradeItemNumber = @currentGtin

          IF (@currentGtin IS NOT NULL AND @currentItem = @item)
               SET @piecesPer = @piecesPer * @quantity
     END

     RETURN (@piecesPer)
END
GO

It's a little complex, but it's not too bad.  The worst thing about it was that it required buy-in between product groups to include this user-defined function in the core database, because my product does not own the database.  I could easily deploy it with my product, but an upgrade to the other product could easily stomp on my user-defined function.  Therefore, I'm stuck making sure that each new release of the other product includes my function.

Another issue with it is that it's very limited.  I'm returning the “pieces per” calculation, but what if I wanted the details for the hierarchical structure?  Let me take a minute to step back and explain what is happening with this calculation.  To make it simple, the concept is that you have different levels, for example pallet, container, and item.  A particular global trade item could be a pallet which means it would contain n number of containers and each container contains m items.  There is no guarantee about the level though, because there could be sub-packs within the containers or the containers might be the ordered items (the bottom level of our search).

The CTE functionality in SQL Server 2005 resolves the latter issue; although arguably it may be more complex.  Here is the CTE and a sample query that returns the hierarchical data (note: the parameter declarations and sets are not shown for brevity).

WITH TradeItemCTE (Gtin, EnterpriseItem, PiecesPer, ChildTradeItem, Level)
AS (
     SELECT G.GlobalTradeItemNumber Gtin, G.EnterpriseItem,
          CAST(C.QuantityOfNextLowerLevelTradeItem * 1 AS float) PiecesPer,
          C.TradeItemIdentificationOfNextLowerLevelTradeItem ChildTradeItem, 1
     FROM GD_ChildTradeItem C
          RIGHT OUTER JOIN GD_GlobalTradeItem G ON C.GlobalTradeItemKey = G.GlobalTradeItemKey
     WHERE G.GlobalTradeItemNumber = @incomingGtin

UNION ALL

     SELECT G.GlobalTradeItemNumber Gtin, G.EnterpriseItem,
          CAST(C.QuantityOfNextLowerLevelTradeItem * TradeItemCTE.PiecesPer AS float) PiecesPer,
          C.TradeItemIdentificationOfNextLowerLevelTradeItem ChildTradeItem, Level + 1
     FROM GD_ChildTradeItem C
          INNER JOIN GD_GlobalTradeItem G ON C.GlobalTradeItemKey = G.GlobalTradeItemKey
          INNER JOIN TradeItemCTE ON TradeItemCTE.ChildTradeItem = G.GlobalTradeItemNumber
     WHERE G.EnterpriseItem = @incomingItem AND ChildTradeItem IS NOT NULL AND Gtin = TradeItemCTE.Gtin
)

SELECT Gtin, EnterpriseItem, PiecesPer, ChildTradeItem, Level FROM TradeItemCTE

The results of running this query may look something like this:

This gives me all the hierarchical data.  The GTIN starting with 2 happens to be the pallet (terra) level, 1 is the container level, and 0 is the item level (nothing follows for the GTIN starting with 0, because it has no children).  I can see from this data that there are 20 containers on a pallet, and 40 items within the top-most level (or pallet).

Another good use I've seen so far for a CTE is with the new pivot table functionality.  I took an example from the “Introducing Microsoft SQL Server 2005 for Developers” book that uses the AdventureWorks database to create the following query.  The book uses a predefined view, but using a CTE negates the need for the view.

WITH ProductSalesCTE (ProductID, ProductName, SalesYear, TotalDue)
AS (
     SELECT P.ProductID, P.Name AS ProductName, Year(O.OrderDate) AS SalesYear, O.TotalDue
     FROM Production.Product AS P
          INNER JOIN Sales.SalesOrderDetail AS OD ON OD.ProductID = P.ProductID
          INNER JOIN Sales.SalesOrderHeader AS O ON O.SalesOrderID = OD.SalesOrderID
)

SELECT * FROM ProductSalesCTE
PIVOT (SUM(TotalDue) FOR SalesYear IN ([2002], [2003], [2004])) AS PVT
WHERE ProductID BETWEEN 779 AND 784

The results of the pivot table are not formatted well, but you can probably guess the results.  You have ProductID, ProductName, 2002, 2003, and 2004 columns with totals under each of the named years and rows for the products between 779 and 784.

Print | posted on Tuesday, February 21, 2006 12:18 PM |