Convert CTE query to nested 1

[firmusgrp]

Hi all,

I have the following CTE query which I need to convert to a nested query. Any help in converting is appreciated.

WITH InvAtA AS (
SELECT SUM(Lin.qty) AS qtyOut, Lin.POLineID, Led.locationID
FROM dbo.tblTRLedger AS Led INNER JOIN dbo.tblTRLine AS Lin ON Led.TRLedgerID = Lin.TRLedgerID
GROUP BY Lin.POLineID, Led.locationID
HAVING (Led.locationID = 1)), 
InvAtO AS (
SELECT Ledger.POLedgerID, Line.transDesc AS chopMark, SUM(Line.unitQty) AS qtyIn, Line.POLineID, Ledger.positionID
FROM dbo.tblPOLedger AS Ledger RIGHT OUTER JOIN dbo.tblPOLine AS Line ON Ledger.POLedgerID = Line.POLedgerID
GROUP BY Ledger.POLedgerID, Line.transDesc, Line.POLineID, Ledger.positionID
HAVING (Ledger.positionID = 1))
SELECT ino.qtyIn - ISNULL(out.qtyOut, 0) AS quantity, ino.POLedgerID
FROM InvAtO AS ino LEFT OUTER JOIN
InvAtA AS out ON ino.POLineID = out.POLineID
WHERE (ino.qtyIn - ISNULL(out.qtyOut, 0) > 0)

 

[gmmastros]

This is relatively easy to do. Just take the CTE parts and move them down to the bottom query, like this:

SELECT ino.qtyIn - ISNULL(out.qtyOut, 0) AS quantity, ino.POLedgerID
FROM   (
       SELECT Ledger.POLedgerID, 
              Line.transDesc AS chopMark, 
              SUM(Line.unitQty) AS qtyIn, 
              Line.POLineID, 
              Ledger.positionID
       FROM   dbo.tblPOLedger AS Ledger 
              RIGHT OUTER JOIN dbo.tblPOLine AS Line 
                ON Ledger.POLedgerID = Line.POLedgerID
       GROUP BY Ledger.POLedgerID, 
              Line.transDesc, 
              Line.POLineID, 
              Ledger.positionID
       HAVING (Ledger.positionID = 1)
       ) AS ino 
       LEFT OUTER JOIN (
         SELECT SUM(Lin.qty) AS qtyOut, 
                Lin.POLineID, 
                Led.locationID
         FROM   dbo.tblTRLedger AS Led 
                INNER JOIN dbo.tblTRLine AS Lin 
                  ON Led.TRLedgerID = Lin.TRLedgerID
         GROUP BY Lin.POLineID, Led.locationID
         HAVING (Led.locationID = 1)
         ) AS out 
         ON ino.POLineID = out.POLineID
WHERE  (ino.qtyIn - ISNULL(out.qtyOut, 0) > 0)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[firmusgrp]

Thanks very much