CODEWITHSUNDEEP
sqloracle-databaseoracle11ghierarchical-query
Random guy
Random guy

Reputation: 923

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?

Upvotes: 1

Views: 47

Answers (2)

Stew Ashton
Stew Ashton

Reputation: 1529

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

Upvotes: 3

Popeye
Popeye

Reputation: 35920

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!!

Upvotes: 0

Related Questions