Confusion in flow of sql output in oracle

Question

I have table T as:

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:

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:

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

score 3 · Accepted Answer · answered Nov 17 '19 at 16:27

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

How is this level going 1 to 3? Can u expalin me how level<=end_range is working? — Random guy, Nov 18 '19 at 02:45
I am not understanding how is this 10rows coming from first example? — Random guy, Nov 18 '19 at 02:50
In my example, I am not going from range_id to range_end, but from 1 to range_end. Since there is no START WITH clause, both rows are starting points. The first row displays once (level 1), then a second time (level 2) because level 2 is not greater than range_end 2. Then the second rows displays because level 3 is not greater than range_end 3. After that, the first output row (level 1) connects to the second row so the second row is displayed at level 2. Then that row connects to itself as level 3. That makes 5 output rows for the first starting point. Same story for the second starting point — Stew Ashton, Nov 20 '19 at 10:47

score 0 · Answer 2 · answered Nov 17 '19 at 16:10

Connect by query don't work as you think.

Its result multiplied when there is different loop condition for each row.

You can use following query to achieve the desired result:

with t (start_range, end_range) as
(select 1,3 from dual union all
select 5,5 from dual union all
select 7,9 from dual)
select start_range, end_range, start_range+a.column_value as num
from t, table(cast(multiset(
                 select level-1 from dual
                 connect by level <= end_range - start_range + 1 ) 
           as sys.odciNumberList)) a

db<>fiddle demo

Cheers!!

Confusion in flow of sql output in oracle

2 Answers2