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