Oracle Hierarchical queries: Translate START WITH ... CONNECT BY PRIOR into 'Recursive Subquery-CodePudding

How would the following START WITH / CONNECT BY hierarchical query look like when translated into a RECURSIVE SUBQUERY FACTORING hierarchical query with WITH clause:

SELECT t1.id
         FROM table1 t1, table2 t2
        WHERE     t1.version_id = t2.id
              AND t1.baseline_date = TRIM (TO_DATE ('2015-05-26', 'yyyy-mm-dd'))
              AND t2.entry_date = t1.baseline_date
   START WITH t1.id IN (SELECT id
                         FROM table1
                        WHERE parent_id = 101015)
   CONNECT BY PRIOR t1.id = t1.parent_id
ORDER SIBLINGS BY t1.child_index;

CodePudding user response：

I think you want:

WITH rsqfc (id, child_index, baseline_date) AS (
  SELECT t1.id,
         t1.child_index,
         t1.baseline_date
  FROM   table1 t1
         INNER JOIN table2 t2
         ON (   t1.version_id = t2.id
            AND t2.entry_date = t1.baseline_date )
  WHERE  t1.parent_id = 101015
UNION ALL
  SELECT t1.id, 
         t1.child_index,
         t1.baseline_date
  FROM   rsqfc r
         INNER JOIN table1 t1
         ON (r.id = t1.parent_id)
         INNER JOIN table2 t2
         ON (   t1.version_id = t2.id
            AND t2.entry_date = t1.baseline_date )
)
SEARCH DEPTH FIRST BY child_index SET order_id
SELECT id
FROM   rsqfc
WHERE  baseline_date = DATE '2015-05-26';

However, without sample data it is difficult to be sure.