Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table loaddefinition (LoadNumber, SectionNumber, BusNumber) as
select 100, 54, 3000 from dual;
1 rows affected

create table switchingdevice (SwitchNumber, FromSectionNumber, ToSectionNumber, NormalSwitchingDeviceState, BusNumber, SwitchName, PseudoSwitch) as
select 1, 54, 105, 'CLOSED', 3000, 'S1', 'N' from dual
union all
select 2, 105, 106, 'CLOSED', 3000, 'S2', 'N' from dual
union all
select 3, 106, 500, 'OPEN', 3000, 'S3', 'N' from dual
union all
select 4, 105, 999, 'CLOSED', 3000, 'S4', 'N' from dual
union all
select 5, 999, 700, 'CLOSED', 3000, 'S5', 'N' from dual;
5 rows affected

create table transformer (TransformerNumber, FromSectionNumber, ToSectionNumber, FromBusNumber, ToBusNumber, TransformerName) as
select 5000, 800, 700, 2000, 3000, 'T1' from dual
union all
select 5001, 801, 701, 2000, 3000, 'T2' from dual;
2 rows affected

with rcte (rootsectionnumber, fromsectionnumber, tosectionnumber) as (
  select fromsectionnumber, fromsectionnumber, tosectionnumber
  from switchingdevice
  where normalswitchingdevicestate = 'CLOSED'
  union all
  select r.rootsectionnumber, sd.fromsectionnumber, sd.tosectionnumber
  from rcte r
  join switchingdevice sd on sd.fromsectionnumber = r.tosectionnumber
)
select rootsectionnumber, tosectionnumber from rcte;


ROOTSECTIONNUMBER
TOSECTIONNUMBER


54
105

105
106

105
999

999
700

54
106

105
500

54
999

105
700

54
500

54
700


with rcte (rootsectionnumber, fromsectionnumber, tosectionnumber) as (
  select fromsectionnumber, fromsectionnumber, tosectionnumber
  from switchingdevice
  where normalswitchingdevicestate = 'CLOSED'
  union all
  select r.rootsectionnumber, sd.fromsectionnumber, sd.tosectionnumber
  from rcte r
  join switchingdevice sd on sd.fromsectionnumber = r.tosectionnumber
)
select ld.loadnumber, t.transformernumber
from loaddefinition ld
join rcte r on r.rootsectionnumber = ld.sectionnumber
join transformer t on t.tosectionnumber = r.tosectionnumber
where ld.loadnumber = 100;


LOADNUMBER
TRANSFORMERNUMBER


100
5000


with rcte (rootsectionnumber, busnumber, fromsectionnumber, tosectionnumber) as (
  select fromsectionnumber, busnumber, fromsectionnumber, tosectionnumber
  from switchingdevice
  where normalswitchingdevicestate = 'CLOSED'
  union all
  select r.rootsectionnumber, r.busnumber, sd.fromsectionnumber, sd.tosectionnumber
  from rcte r
  join switchingdevice sd on sd.fromsectionnumber = r.tosectionnumber
  and sd.busnumber = r.busnumber
)
select ld.loadnumber, t.transformernumber
from loaddefinition ld
join rcte r on r.rootsectionnumber = ld.sectionnumber
and r.busnumber = ld.busnumber
join transformer t on t.tosectionnumber = r.tosectionnumber
and t.tobusnumber = r.busnumber
where ld.loadnumber = 100;


LOADNUMBER
TRANSFORMERNUMBER


100
5000