Hi,
I know subject is not very explanatory. I need all combination as separate rows in a hierarchical query. is there an easy way to do it? here is an example (I use live sql for sample data)
select lpad(' ', level*2, ' ') || first_name as first_name, sys_connect_by_path(employee_id, '=>') path
from hr.employees e
connect by prior employee_id = manager_id
start with manager_id is null;
Steven =>100
Neena =>100=>101
Nancy =>100=>101=>108
Daniel =>100=>101=>108=>109
...
I suppressed some of the output. second column is connect by path until that row as you know. I need to get all rows in all paths as rows.
every path (for ex: =>100=>101=>108=>109) should be returned as rows starting from 100 to 109.
for the example above desired output:
100 steven...
100 steven...
101 neena...
100 steven...
101 neena...
108 nancy...
100 steven...
101 neena...
108 nancy...
109 daniel...
I added extra empty lines to explain it better. I probably will use whole columns in a row so just parsing sys_connect_by_path result and returning as rows (using a lateral or something) is not enough for me. Also, I can use lateral to parse =>100=>101=>108=>109 and then join the main table again but it would be a little bit performance killer.
since, in hierarchical query, we are reading all rows, is there a way to generate those rows without parsing sys_connect_by_path result or re joining the main table?
thanks.