1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
| WITH TABLE1 AS
(
select 1 as ID, 5 as ID_TBL2 from dual union all
select 2 , 1 from dual
),
TABLE2 AS
(
select 1 as ID, 0 AS ID_P, 'dec1' AS txt from dual union all
select 2 , 1 , 'dec2' from dual union all
select 5 , 2 , 'dec3' from dual union all
select 0 , NULL , 'root' from dual
),
HIER AS
(
select
connect_by_root id as id_init,
id,
txt
from
TABLE2
start with
ID in (select ID_TBL2 from TABLE1)
connect BY
ID = prior ID_P
)
select
T1.ID,
HI.ID,
HI.txt
from
TABLE1 T1
inner join HIER HI
on HI.id_init = T1.ID_TBL2
order by
T1.ID asc,
HI.ID desc;
ID ID_1 TXT
1 5 dec3
1 2 dec2
1 1 dec1
1 0 root
2 1 dec1
2 0 root |
Partager