Search code examples
sqloracleoracle11ghierarchical-query

Confusion in flow of sql output in oracle

I have table T as:

enter image description here

So,I got sql from somewhere as:

select * from (
SELECT start_range,(LEVEL + START_RANGE)-1 NUM,end_range
   FROM offc.T
  CONNECT BY (LEVEL +START_RANGE ) <= END_RANGE+1) order by start_range,num,end_range;

I got output as:

enter image description here

I am describing how is this query running now:

At first level=1 so, start_range=1 and end_range=3 and it loops upto 1 to 3; So,the output is:

start_range | num | end_range
   1          1       3       2<=4,level=1
   1           2       3      3<=4,level=2
   1           3       3      4<=4,level=3
and the new start_range=5 and end_range=5.

But,I am getting confuse how is this loop going on? I am seeing large no of rows which has 1 2 3 data as:

enter image description here

How is this 1 2 3 rows coming more than once ? Can anyone help me to understand the flow of this sql?

Solution

  • Suppose I have two rows and I want to expand both of them to get one row for every integer in the range. For example, take this table:

    drop table U purge;

create table U as
select 1 range_id, 2 range_end from dual
union all
select 2, 3 from dual;

select * from u;
ID  END
1   2
2   3

    If I try something like your attempt:

    select range_id, range_end, level
from u
connect by level <= range_end;

ID  END  LEVEL
1   2   1
1   2   2
2   3   3
2   3   2
2   3   3
2   3   1
1   2   2
2   3   3
2   3   2
2   3   3

    What is this mess? It looks like I’m starting with each row and connecting to the other row – which makes sense because I’m not saying to stay on the same row. Let’s try again:

    select range_id, range_end, level
from u
connect by level <= range_end
and range_id = prior range_id

Error report - SQL Error: ORA-01436: CONNECT BY loop in user data

    Now I made a reference to something PRIOR – the range_id. Oracle sees that the same range_id is accessed twice in a row, so it assumes there is an infinite loop and aborts the execution.

    There is a way to avoid that error, using the NOCYCLE keyword:

    select range_id, range_end, level
from u
connect by nocycle level <= range_end
and range_id = prior range_id;

ID  END LEVEL
1   2   1
2   3   1

    Well, I didn’t get the error, but Oracle still considers that doing the same range_id twice would be a loop, so it stops first.

    What we need is to add something to the prior row that will make Oracle think it is different. SYS_GUID() is a very low-cost function that returns a nonrepeating value. If we refer to PRIOR SYS-GUID() in a condition, that is enough to make the prior row unique and to prevent the perception of an infinite loop.

    select range_id, range_end, level
from u
connect by level <= range_end
and range_id = prior range_id
and prior sys_guid() is not null;

ID  END LEVEL
1   2   1
1   2   2
2   3   1
2   3   2
2   3   3

    Applying this technique to your data:

    with data(start_range, end_range) as (
select 1, 3 from dual
union all select 5, 7 from dual
)
SELECT start_range, end_range,
  start_range + level - 1 num
FROM data
CONNECT BY start_range + level - 1 <= END_RANGE
and start_range = prior start_range
and prior sys_guid() is not null;

    The other answer works too! I am just trying to explain how CONNECT BY works.

    Best regards, Stew Ashton