Geeks With Blogs
Bunch's Blog One day I'll have a catchy subtitle, one day

A great benefit of using a CTE is their ability to replace some uses of Cursors. Sometimes you may need a Cursor to fetch an ID value from a table. Use that ID in a Select statement. Then fetch the next ID value, use that in the same Select statement over and over again until you ran through all the ID values. A CTE can help eliminate that and from what I can see is generally a bit faster than using a Cursor.

This example is using a table to hold purchase orders (tblPO) and another table to hold invoices (tblInv). tblPO has columns for the purchase order ID (POID), the purchase order number (PONum) and the amount of funds in the purchase order (POAmt). tblInv has columns for the associated purchase order (POID) and an amount for the invoice (InvAmt). The scenario is that you need a list of all purchase order IDs, numbers, amount of the PO and the amount that is still available. To calculate the amount available you need to total up all the invoice amounts for a purchase order and then subtract them from the purchase order amount.

WITH cte(POID, PONum, POAmnt) AS (
SELECT POID, PONum, POAmt FROM tblPO )

SELECT POID, PONum, POAmt,
(POAmt - (SELECT SUM(InvAmt)FROM tblInv WHERE POID = cte.POID)) AS 'Available'
FROM cte

You setup the CTE with the values you want except for the one that needs each POID individually (the amount available calculation). Then when you run a select from the CTE you can put the current POID from the CTE in the where clause. This ends up being a lot simpler and faster than setting up a Cursor and fetching each POID from tblPO.

A second way to set this up which accomplishes the same would be to create the CTE with just the ID you want to cycle through. Either way it works the same it just depends on how you like to write it.

WITH cte(POID) AS (
SELECT POID FROM tblPO)

SELECT cte.POID, po.PONum, po.POAmnt, (POAmt - (SELECT SUM(InvAmt)FROM tblInv WHERE POID = cte.POID)) AS 'Available'
FROM cte
INNER JOIN tblPO po ON po.POID  = cte.POID

Technorati Tags:

 

Posted on Wednesday, April 13, 2011 1:05 PM | Back to top


Comments on this post: Cycling Through Selected CTE Values

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Bunch | Powered by: GeeksWithBlogs.net