+1 on a RECURSIVE not working as expected?
I'm trying this query in Redshift:
WITH RECURSIVE CTE AS
(
SELECT id
, managerid
, name
, 1 AS level
FROM users parent
WHERE name = 'Jason Ramsay'
UNION ALL
SELECT child.id
, child.managerid
, child.name
, level +1
FROM users child, users parent
WHERE child.managerid = parent.id
)
SELECT *
FROM CTE
but I keep getting this error: (500310) Invalid operation: column "level" does not exist in child, parent; 1 statement failed.
What am I doing wrong? According to the documentation I found here, Redshift accepts recursive and this is the way to go about it: https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html#r_WITH_clause-recursive-cte
Solution 1:
I believe that for recursive cte, the cte itself must be referenced in the union all
part:
WITH RECURSIVE cte AS (
SELECT id
, managerid
, name
, 1 AS level
FROM users parent
WHERE name = 'Jason Ramsay'
UNION ALL
SELECT child.id
, child.managerid
, child.name
, level + 1
FROM cte parent -- <<-- this
JOIN users child ON parent.id = child.managerid
)
SELECT *
FROM cte
The documentation you linked to clearly mentions this:
recursive_query A UNION ALL query that consists of two SELECT subqueries:
The first SELECT subquery doesn't have a recursive reference to the same CTE_table_name. [...]
The second SELECT subquery references the same CTE_table_name in its FROM clause. [...]