Oracle SQL hierarchical query to trace cable segments

Question

I have a dataset that can be represented as below network diagram where cable segments form the hierarchy:

The first segment is the input cable for a box and is tagged 'I', and the same box (addressId) will have out cable (tagged 'O') that would go and connect to another box at a different location. The out cable is termed as In cable at the second box.

I have SQL that can give me segment-1 data, and if I add another layer with subquery I get segment-2 and union of these will give me segment-1 and segment-2 cable information. But if there are N number of segments in the network then my query gets complicated too fast. I wanted to find out if there is a way to rewrite these below queries such a way that it can retrieve all the segments regardless of the segment size and without having to write unions for each segments.

-- 1 Segment list with the subject cable and pair range as Incount
select region,cable,lowRange,highRange,address,addressId 
      from schema.TableName
      where upper(region) = 'COLORADO' and
            upper(cable) = 'ABCD' and
            to_number(lowRange) >= 1 and
            to_number(highRange) <= 600 and
            side = 'i';

-- 2 Segment list with 1 Segment cable and pair range Outcount as Incount
select c.region,c.cable,c.lowRange,c.highRange,c.address,c.addressId
      from schema.TableName c,
           --1 Segment list  which have Outcount of their own
           (select a.region,a.address as xbox,a.cable,a.lowRange,a.highRange 
            from schema.TableName a, 
                 --1 Segment list with the subject cable and pair range as Incount
                 (select region,addressId 
                          from schema.TableName
                          where upper(region) = 'COLORADO' and
                            upper(cable) = 'ABCD' and
                            to_number(lowRange) >= 1 and
                            to_number(highRange) <= 600 and
                                side = 'i') b
            where a.region = b.region and
                  a.addressId = b.addressId and
                  a.side = 'o') d
      where c.region = d.region and
            c.ca = d.ca and
            c.side = 'i' and
            ((c.lowRange between d.lowRange and d.highRange) or
             (c.highRange between d.lopr and d.highRange));

My knowledge on hierarchical joins is limited, I came up with below but of course it doesn't work as intended. And I suspect it might be due to CONNECT BY PRIOR cannot account and not account box location (addressId) selectively (cable that is input to a box and output from the same box will have same addressId whereas the same cable tagged as input at different box will have different addressid) or maybe this needs recursive query logic.

Much appreciated if anyone can provide me pointers.

SELECT DISTINCT 
    t.region, t.cable, t.lowRange, t.highRange, t.address, t.addressId,
    LEVEL AS segment_level
FROM
    schema.TableName t
WHERE
    t.side = 'i'
CONNECT BY
    PRIOR t.region = t.region
    AND PRIOR t.cable = t.cable
    AND PRIOR t.side = 'o'
    AND t.addressId <> PRIOR t.addressId
    AND (
        (t.lowRange BETWEEN PRIOR t.lowRange AND PRIOR t.highRange)
        OR
        (t.highRange BETWEEN PRIOR t.lowRange AND PRIOR t.highRange)
    )
START WITH
    UPPER(t.region) = 'COLORADO'
    AND UPPER(t.cable) = 'ABCD'
    AND TO_NUMBER(t.lowRange) >= 1
    AND TO_NUMBER(t.highRange) <= 600
    AND t.side = 'i';

========================

Update: Please find the DB fiddle below. The expected result should have all the segments available on the table of side 'i'. I have included 'segmentlevel' as an additional column just to help with the identification here, but in reality this column wont exist on the table.

Also the query I have with the unions provide upto segment '2' only because I have added in just 2 levels of union.

https://dbfiddle.uk/WpmHfcpk

Answer 1

I'd say that recursion cannot alternate os and is, you have to chain rows of the same model (one o + one i). But our "model" doesn't have to be a table: it can be the JOIN of two tables.

So instead of recursing over an o, then an i, then an o, then an i, let's group those two by two and recurse over DATA_SAMPLE o JOIN DATA_SAMPLE i ON o.side = 'o' and i.side = 'i'and /* everything else needed to get a real match */, until there's no more of those i/o link to add to the chain.

I switched from Oracle's proprietary CONNECT BY to the more standard SQL recursive CTE, that I find easier to read and also write.
a quick mapping from CONNECT BY to recursive CTE is:

with recursive_alias(col1, col2, col3) as
(
  select … from source_table -- This corresponds to START WITH
  union all
  select …
  from recursive_alias -- Refering to the alias means PRIOR
  join source_table on source_table.… = recursive_alias.… -- Pick up a new row.
  -- As we are on a regular from and join, we can continue joining tables:
  join another_source_table on … -- In our case, as we said that our model was two tables, we will join to another occurrence of DATA_SAMPLE.
)
select * from r;

With this in mind, and reusing the a, b and c table aliases of your fiddle, I got:

with
  chain(region,cable,lowRange,highRange,address,addressId ,side, segmentlevel) as
  (
    select region,cable,lowRange,highRange,address,addressId ,side, segmentlevel
    from DATA_SAMPLE
    where region = 'colorado' and
          upper(cable) = '6' and
          lowRange >= 500 and
          highRange <= 600 and
          side = 'i'
    
    union all -- After that union all comes the recursive part: the chain table alias (that we realias as b) holds last iteration's rows
              -- (that is, for iteration 1: the rows coming from iteration 0 above)
    
    select c.region,c.cable,c.lowRange,c.highRange,c.address,c.addressId,c.side, c.segmentlevel
    from chain b, DATA_SAMPLE a, DATA_SAMPLE c
    where a.region = b.region and
          a.addressId = b.addressId and
          a.side = 'o'
      and c.region = a.region and
          c.cable = a.cable and
          c.side = 'i' and
          ((c.lowRange between a.lowRange and a.highRange) or
           (c.highRange between a.lowRange and a.highRange))
  )
select * from chain
order by segmentlevel, side, lowrange, highrange, address;

(I kept the table1, table2 where table1.… = table2.… structure to make it easily mappable to your original query, but switching to a table1 join table2 on table1.… = table2.… model would avoid the risk of forgetting a join condition, as the on keyword is mandatory)

Thanks to your fiddle, I could make it run on your data and got exactly the same result as your 2 levels… plus segmentlevel 3 of course (I did a diff -u to make sure of it):

REGION	CABLE	LOWRANGE	HIGHRANGE	ADDRESS	ADDRESSID	SIDE	SEGMENTLEVEL
colorado	6	501	600	address14	1453	i	1
colorado	cp111s6	1	50	address194	1475	i	2
…
colorado	cp111s6	276	350	address254	535	i	2
colorado	cp111s6	276	350	address334	479	i	2
colorado	cprs301	1	5	address684	233	i	3
colorado	cprs301	1	5	address694	234	i	3
…
colorado	211s6th	56	71	address504	112	i	3
colorado	313s6th	61	75	address434	203	i	3