How To Write Involved Recursive Subquery In Sqlplus W/ Multiple Tables To Trace Nodes?

I am trying to create a report that maps loads on a circuit to their respective power source (Transformer). All of the devices are in separate tables and interconnected in the data

Solution 1:

In your simplified example (sort of, I started to use your real table names, but it's the original data) you can get all of the routes through multiple switches with:

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

ROOTSECTIONNUMBER TOSECTIONNUMBER
----------------- ---------------5410510510610599999970054106105500549991057005450054700

one of which has an end-to-end route from section 54 to 700; and you can then join that to the load definition:

withrcte (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
---------- -----------------
       1005000

Or if you need to include the busnumber in the logic (based on a brief look at your larger query...):

withrcte (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
---------- -----------------
       1005000

db<>fiddle

Adding in your other tables will obviously complicate things a bit, but it isn't clear quite how they're all related.