0

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;
Spanky
  • 111
  • 9
  • https://riptutorial.com/sql/example/5603/oracle-connect-by-functionality-with-recursive-ctes – p3consulting Jan 23 '23 at 15:23
  • `start with` is an anchor member - the first (non-recursive) part of `with`. `connect by` is a `join` conditions in the recursive member of `with` where `prior` is an equivalent of the reference to the `join`ed CTE itself (which essentially makes it recursive) – astentx Jan 23 '23 at 16:06

1 Answers1

0

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.

MT0
  • 143,790
  • 11
  • 59
  • 117