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
Advertisement
Answer
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. […]