By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table_1 (fk_1 int, fk_2 int, rel_cd varchar2(6));
insert into table_1 (fk_1, fk_2, rel_cd) values (345, 123, 'ORG');
1 rows affected
insert into table_1 (fk_1, fk_2, rel_cd) values (567, 345, 'ORG');
1 rows affected
insert into table_1 (fk_1, fk_2, rel_cd) values (897, 567, 'ORG');
1 rows affected
create table org (fk int, org varchar2(6), cd varchar2(6));
insert into org (fk, org, cd) values (123, 'Global', 'A_001');
1 rows affected
insert into org (fk, org, cd) values (345, 'Canada', 'A_002');
1 rows affected
insert into org (fk, org, cd) values (567, 'AB', 'A_003');
1 rows affected
insert into org (fk, org, cd) values (897, 'VAN', 'A_004');
1 rows affected
with
n (id, lvl) as (
select fk, 1 from org a where not exists (select fk_1 from table_1 b where b.fk_1 = a.fk)
union all
select t.fk_1, n.lvl + 1
from n
join table_1 t on t.fk_2 = n.id
)
select o.fk, o.org, n.lvl, o.cd
from org o
left join n on n.id = o.fk
order by o.fk -- optional ordering
| FK | ORG | LVL | CD |
|---|---|---|---|
| 123 | Global | 1 | A_001 |
| 345 | Canada | 2 | A_002 |
| 567 | AB | 3 | A_003 |
| 897 | VAN | 4 | A_004 |