Recursive/Hierarchical Query

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( --base case SELECT supervisor_name, name, 1 AS HierarchyLevel, emplid, supervisor_id FROM emps WHERE NAME = 'Bauer, Jack' UNION ALL -- Recursive step SELECT r.supervisor_name, r.NAME, pr.HierarchyLevel + 1 AS HierarchyLevel, r.emplid, r.supervisor_id FROM emps r INNER JOIN parentchildrels pr ON pr.emplid = r.supervisor_id) SELECT supervisor_name, name, hierarchylevel FROM ParentChildRels 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?
user
12-08-2014 08:37:44 -0400

5 Answers

Hi, at this moment recursive common table expressions are not supported, so the temporary view 'parentChildRels' cannot be referenced inside its definition. Regards.
Denodo Team
12-08-2014 12:06:35 -0400
Ok thanks - will look to see if we can do it in a SQL Server view and pull it in as a base view
user
12-08-2014 12:08:07 -0400
I have run into this issue as well. Hopefully full support for recursive CTE is on the roadmap. Regards
user
04-09-2014 18:19:35 -0400
Hi, We also ran into same issue. We could able to achieve desired result in Oracle using "CONNECY BY" hierarchical but not finding any equivalent function(s) in Denodo with 6.0 with 20160905 update. We would be prefer to use direct function (if available) in Denodo instead of looking at alternate options. Looking forward for response. Thanks in advance. Regards.
user
14-08-2017 06:11:36 -0400
Hi, recursive queries are not supported right now but I confirm they are on the Roadmap. Hope this helps!
Denodo Team
21-08-2017 03:13:46 -0400
You must sign in to add an answer. If you do not have an account, you can register here