CODEWITHSUNDEEP
sqlsql-serversql-server-2012
samb90
samb90

Reputation: 1073

SQL Server CTE query producing unexpected result

I have a CTE like this:

WITH CTE(PartNum, RowIndex) AS
(
        -- Anchor Member
        SELECT
            PartNum
            ,CAST(OurQty AS INT) AS RowIndex
        FROM dbo.RcvDtl
        WHERE OurQty > 0
        UNION ALL
        -- Recursive Member
        SELECT
            PartNum
            ,RowIndex - 1
        FROM CTE
        WHERE RowIndex - 1 > 0
)
SELECT 
    tbl1.PONum, tbl1.PartNum, 
    CAST(tbl1.OurQty AS INT) AS Quantity, 
    tbl2.RowIndex AS RowIndex
FROM 
    dbo.RcvDtl AS Tbl1
INNER JOIN 
    CTE AS Tbl2 ON Tbl1.PartNum = Tbl2.PartNum
WHERE 
    tbl1.PONum = '63'
ORDER BY 
    Tbl1.PartNum OPTION(MAXRECURSION 1000)

Which runs on the following table:

| PartNum | Quantity
---------------------
  10050857    6
  10050886    10
  10050923    24

The idea of the query is to duplicate a record (PartNum) based on its quantity. The query duplicates the first PartNum '10050857' by 46 records (6+(6+10+24)) when it should just duplicate 6 times, the second PartNum '10050886' by 10 records as expected and the final part num '10050923' by 24 records as expected.

Why is the query duplicating the first record by an accumulation of the records following the first record?

Upvotes: 3

Views: 336

Answers (1)

MikeAinOz
MikeAinOz

Reputation: 128

I had to change the names a little, but your CTE does work:

WITH CTE(PartNum, RowIndex) AS
(
        -- Anchor Member
        SELECT
            PartNum
            ,CAST(OurQty AS INT) AS RowIndex
        FROM dbo.RcvDtl
        WHERE OurQty > 0
        UNION ALL
        -- Recursive Member
        SELECT
            PartNum
            ,RowIndex - 1
        FROM CTE
        WHERE RowIndex - 1 > 0
)
SELECT CTE.PartNum, dbo.RcvDtl.OurQty, CTE.RowIndex from CTE INNER JOIN dbo.RcvDtl
ON CTE.PartNum = dbo.RcvDtl.PartNum
order by PartNum,RowIndex

Just check your code again, it will work. Nice trick too, I love a bit of CTE!

Upvotes: 1

Related Questions