Hello experts,
This is in fact a child thread in addition to the one that I created below:
Weekend fun with Knight's Tour 8x8 solution in SQL
Anyone, who wants to know the background of this new thread can refer to the above one.
I was trying to understand the observations that @"mathguy" made when @"Frank Kulash" used rownum in the solution for Knight's Tour in the above thread (In here Frank said that rownum is nothing but the path). And hence I used some code below:
with
inputs(node_id, parent_node_id) as
(
select 1, null from dual union all
select 2, 1 from dual union all
select 3, 2 from dual union all
select 4, 3 from dual union all
select 5, 4 from dual union all
select 6, 5 from dual union all
select 7, 6 from dual union all
select 8, 3 from dual union all
select 9, 8 from dual union all
select 10, 9 from dual union all
select 20, 10 from dual union all
select 11, 3 from dual union all
select 12, 11 from dual union all
select 13, 12 from dual union all
select 14, 13 from dual
)
select ltrim(sys_connect_by_path(node_id,'->'),'->') as path, level lvl--, rownum as rn
from inputs
where level = 7
start with parent_node_id is null
connect by prior node_id = parent_node_id;
The above is the Tree stucture. As it is indicated there are three paths from the root node.
In the thread above @"Stew Ashton" mentioned in Reply 23 the below points from the doc.
- If we put a condition in CONNECT BY and that condition is not met, the row is not returned and Oracle does not search for its descendants.
- If we put a condition in WHERE and that condition is not met, the row is eliminated but all the descendants may still be returned if they meet the condition.
And hence I considered taking only the first two paths ie Path 1 and Path 2. But not traversing the last path i.e Path 3. That leaves with the below code.
select ltrim(sys_connect_by_path(node_id,'->'),'->') as path, level lvl--, rownum as rn
from inputs
where level = 7
start with parent_node_id is null
connect by prior node_id = parent_node_id
and level <= 7 and rownum <= 2;
And here, as Stew said I assume that the Path 3 is not traversed as rownum is mentioned in the connect by clause. In order to test it, I created sequence and function and then used it like below.
create sequence s;
create or replace function dummy_func return number as
begin
return s.nextval;
end;
with
inputs(node_id, parent_node_id, s) as
(
select 1, null, dummy\_func() from dual union all
select 2, 1, dummy\_func() from dual union all
select 3, 2, dummy\_func() from dual union all
select 4, 3, dummy\_func() from dual union all
select 5, 4, dummy\_func() from dual union all
select 6, 5, dummy\_func() from dual union all
select 7, 6, dummy\_func() from dual union all
select 8, 3, dummy\_func() from dual union all
select 9, 8, dummy\_func() from dual union all
select 10, 9, dummy\_func() from dual union all
select 11, 10, dummy\_func() from dual union all
select 12, 3, dummy\_func() from dual union all
select 13, 12, dummy\_func() from dual union all
select 14, 13, dummy\_func() from dual union all
select 15, 14, dummy\_func() from dual
)
select ltrim(sys_connect_by_path(node_id||'('||s||')','->'),'->') as path, level lvl, rownum as rn
from inputs
where level = 7
start with parent_node_id is null
connect by prior node_id = parent_node_id
and level <= 7 and rownum <= 2;
And then I ran the below code to check the sequence.
Surprisingly I got the 16 as the value instead of 12. This proves that the third path ie, Path 3 is also traversed. Then I don't understand the working of rownum in the connect by clause.
Can someone please explain how exactly rownum in connect by works ?
Thanks in advance.
I am using the below DB version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Regards,
Ranagal