CODEWITHSUNDEEP
sql-serverrecursionsubquery
BenOfTheNorth
BenOfTheNorth

Reputation: 2872

Using WITH in subquery (MS SQL)

I'm trying to perform some recursion inside a subquery, but I don't seem to be able to use WITH - Have I missed something or is there a workaround perhaps?

WHERE swn.stocknode_id in 
(
    WITH Hierachy(stocknode_id, short_desc, PARENTNODE_ID, level)

    AS

    (
        SELECT 
            a.stocknode_id,
            0 AS level

        FROM stock_website_node AS a  

        WHERE a.short_desc = 'XXXXXXXX'
        AND a.PARENTNODE_ID = 0

        UNION ALL

        SELECT 
            a.stocknode_id,
            ch.level + 1

        FROM stock_website_node AS a

        INNER JOIN Hierachy ch
        ON a.PARENTNODE_ID = ch.stocknode_id
    )

    SELECT 
        stocknode_id

    FROM Hierachy

    WHERE level > 0
)

I'm met with:

Incorrect syntax near the keyword 'WITH'.

Upvotes: 5

Views: 35767

Answers (2)

AlexK
AlexK

Reputation: 9927

;WITH Hierachy(stocknode_id, short_desc, PARENTNODE_ID, level) AS  (
    SELECT 
        a.stocknode_id,
        0 AS level
    FROM stock_website_node AS a  
    WHERE a.short_desc = 'XXXXXXXX'
    AND a.PARENTNODE_ID = 0

    UNION ALL

    SELECT 
        a.stocknode_id,
        ch.level + 1
    FROM stock_website_node AS a
    INNER JOIN Hierachy ch
    ON a.PARENTNODE_ID = ch.stocknode_id
)

SELECT *
FROM swn  -- BIGQUERY...
WHERE swn.stocknode_id in 
(
SELECT 
    stocknode_id
FROM Hierachy
WHERE level > 0
)

Upvotes: 5

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

Common Table expressions have to be at the top level.

You need to take the CTE out of the WHERE clause and just refference the stocknode_id in the WHERE like this:

WHERE swn.stocknode_id in (SELECT stocknode_id FROM Hierachy WHERE level > 0)

Upvotes: 7

Related Questions