Using WITH in subquery (MS SQL)

Question

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'.`

Accepted Answer

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)
```

Using WITH in subquery (MS SQL)

Solution

Problem