You can translate the question and the replies:

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?
12-08-2014 08:37:44 -0400

7 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
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
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.
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
Hi, is it supported in Denodo 8.0?
17-11-2020 03:28:30 -0500
Hi, I don't believe the Recursive CTE is introduced in Denodo 8.0, this I infer by reading the [Denodo Platform New Features Guide]( section of the Denodo Documentation. As mentioned in the previous response, the Recursive CTE is part of the roadmap and it would help if you are a valid support user to raise a enhancement request to add interest for the request. Hope this helps!
Denodo Team
20-11-2020 04:49:45 -0500
You must sign in to add an answer. If you do not have an account, you can register here