Geek Answers Handbook

Using WITH in a Subquery (MS SQL)

I am trying to do some recursion inside a subquery, but it seems like I cannot use WITH. Am I missing something or maybe a workaround?

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 meet:

Incorrect syntax near the keyword 'WITH'.

+4
source share
2 answers

General table expressions should be at the top level.

You need to deduce the CTE from the sentence WHEREand simply repeat the stocknode_id file WHEREas follows:

WHERE swn.stocknode_id in (SELECT stocknode_id FROM Hierachy WHERE level > 0)
+5
source 
;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
)
+2
source

More articles:


All Articles