Menu
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'.
402
The WITH clause is for subquery factoring, also known as common table expressions or CTEs: The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying query_name.
The WITH clause in SQL was introduced in standard SQL to simplify complex long queries, especially those with JOINs and subqueries. Often interchangeably called CTE or subquery refactoring, a WITH clause defines a temporary data set whose output is available to be referenced in subsequent queries.
You cannot include text, unitext, or image datatypes in subqueries. Subqueries cannot manipulate their results internally, that is, a subquery cannot include the order by clause, the compute clause, or the into keyword.
Answer: C. Multi-row operators cannot be used in single-row sub-queries and vice versa.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
