1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| WITH INV_HIERARCHIES AS
(
SELECT 1 AS id,null AS child_id FROM dual union ALL
SELECT 2 , 1 FROM dual union ALL
SELECT 3 , 1 FROM dual union ALL
SELECT 4 , 2 FROM dual
),
INV_INVESTMENTS AS
(
SELECT 1 AS id,'Durand' AS name FROM dual union ALL
SELECT 2 ,'Dufour' FROM dual union ALL
SELECT 3 ,'Cheng' FROM dual union ALL
SELECT 4 ,'Moise' FROM dual
)
select hier,lv,name
from
(
SELECT SYS_CONNECT_BY_PATH(ih.child_id, ' - ') as hier, level as lv, ih.id
FROM INV_HIERARCHIES ih
start WITH ih.id = 1
connect BY prior ih.id = ih.child_id
) req JOIN INV_INVESTMENTS ii ON ii.id = req.id |
Partager