Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

wrong result of 11gR2 Recursive with clause part2

Aketi JyuuzouApr 17 2010 — edited Apr 17 2010
This thread is continued from 1056886
and 1057057
select * from v$version;

BANNER
-------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
create table siblingsT(
ID    number primary key,
OyaID number);

insert into siblingsT values(1,null);
insert into siblingsT values(3,   1);
insert into siblingsT values(5,   3);
commit;
select connect_by_root ID as treeID,ID,OyaID
  from siblingsT
start with OyaID is null
connect by prior ID = OyaID;

treeID  ID  OyaID
------  --  -----
     1   1   null
     1   3      1
     1   5      3
OK I will emulate connect_by_root in recursive with clause :-)
with rec(treeID,ID,OyaID) as(
select ID,ID,OyaID
  from siblingsT
 where OyaID is null
union all
select a.ID,b.ID,b.OyaID
  from rec a,siblingsT b
 where a.ID = b.OyaID)
select * from rec;

treeID  ID  OyaID
------  --  -----
     1   1   null
     1   3      1
     3   5      3
But I think correct result is below :-(
Why treeID of third row is 3 ?
treeID  ID  OyaID
------  --  -----
     1   1   null
     1   3      1
     1   5      3
****************************************************************************************************
SomeHow below SQL which exchange a.ID to a.TreeID returns correct result.
But I think this is a bug B-)
with rec(treeID,ID,OyaID) as(
select ID,ID,OyaID
  from siblingsT
 where OyaID is null
union all
select a.treeID,b.ID,b.OyaID
  from rec a,siblingsT b
 where a.ID = b.OyaID)
select * from rec;

treeID  ID  OyaID
------  --  -----
     1   1   null
     1   3      1
     1   5      3
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 15 2010
Added on Apr 17 2010
1 comment
1,263 views