My Database is
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options
I have a table with a self join that represents hierarchical data (master/detail) like this:
with test_data as(
select 1000*level+1 as id
, 'name_'||level
, 1000* (5*ceil(level/5))+1 as ref_id
from dual connect by level < 21
)
select * from test_data
connect by nocycle ref_id =prior id
start with id = 5001
-- returns:
5001 name_5 5001
1001 name_1 5001
2001 name_2 5001
3001 name_3 5001
4001 name_4 5001
what I need is to select the details id (e.g. 1001) and get the detail row plus the master row with ID 5001:
expected output:
5001 name_5 5001
1001 name_1 5001
if I simply change the ID in the START WITH part I get only one row as expected. But shifting the PRIOR key word to the other side of the equal sign (=) does not result in the master row returned additionally:
with test_data as(
select 1000*level+1 as id
, 'name_'||level
, 1000* (5*ceil(level/5))+1 as ref_id
from dual connect by level < 21
)
select * from test_data
connect by nocycle prior ref_id = id
start with id = 1001
-- returns
1001 name_1 5001
How do I get the master row additionally to the selected detail row?