oracle-databasehierarchical-datarecursive-queryconnect-by

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


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;

Solution

  • 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.