We are looking to perform a hierarchical/recursive query similar to a CONNECT BY query in Oracle or a CTE in SQL Server. Here is a working query in SQL Server that we are attempting to reproduce in Denodo. Denodo understands the "with" clause, but not the recursive join back to itself. I get an error that the "parentchildrels" view can't be found.
WITH ParentChildRels AS(
1 AS HierarchyLevel,
WHERE NAME = 'Bauer, Jack'
-- Recursive step
pr.HierarchyLevel + 1 AS HierarchyLevel,
FROM emps r
INNER JOIN parentchildrels pr ON pr.emplid = r.supervisor_id)
SELECT supervisor_name, name, hierarchylevel
The output would basically be a listing of manager => employee. Ultimately an lpad would be applied to create indenting to show the levels.
Is this possible in Denodo?