By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TABLE_1 ( FK_1, FK_2, REL_CD ) AS
SELECT 345, 123, 'ORG' FROM DUAL UNION ALL
SELECT 567, 345, 'ORG' FROM DUAL UNION ALL
SELECT 897, 567, 'ORG' FROM DUAL;
3 rows affected
CREATE TABLE org( FK, ORG, CD ) AS
SELECT 123, 'Global', 'A_001' FROM DUAL UNION ALL
SELECT 345, 'Canada', 'A_002' FROM DUAL UNION ALL
SELECT 567, 'AB', 'A_003' FROM DUAL UNION ALL
SELECT 897, 'VAN', 'A_004' FROM DUAL;
4 rows affected
SELECT FK,
ORG,
LVL,
CD
FROM (
SELECT 1 AS LVL, -- Generate a row for the parent at the root.
123 AS FK_1
FROM DUAL
UNION ALL
SELECT LEVEL+1,
FK_1 -- Leaf value of this rather than root.
FROM TABLE_1
START WITH FK_2=123 -- Start with this rather than end with it.
CONNECT BY NOCYCLE
PRIOR FK_1 = FK_2 -- Swap these.
)
INNER JOIN ORG
ON ORG.FK= FK_1;
| FK | ORG | LVL | CD |
|---|---|---|---|
| 123 | Global | 1 | A_001 |
| 345 | Canada | 2 | A_002 |
| 567 | AB | 3 | A_003 |
| 897 | VAN | 4 | A_004 |