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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

connect_by_isleaf - oracle 9i ?

591942Sep 3 2007 — edited Nov 6 2010
Hi All,

Am using Oracle 9i. And My requrement is to know the element is a leaf or node.
In oracle 10g we have connect_by_isleaf for this purpose.
How can I achieve the same in Oracle 9i.

Please help me...

Comments

572471
Aketi Jyuuzou
My alternative solutions.
***Sample***
ID  PrevID
--  ------
 1    null
 2       1
 3       1
 4       3

***output which we want***
ID  PrevID  isLeaf
--  ------  ------
 1    null       0
 2       1       1
 3       1       0
 4       3       1

***DDL***
create table CloneIsLeaf as
select 1 as ID,null as PrevID from dual
union select 2,1 from dual
union select 3,1 from dual
union select 4,3 from dual;

--method1
select ID,PrevID,
case when exists(select 1 from CloneIsLeaf b
                  where a.ID = b.PrevID) then 0 else 1 end as isLeaf
  from CloneIsLeaf a
Start With ID = 1
connect by prior ID = PrevID;

--method2
select ID,PrevID,
case when Level < Lead(Level) over(order by RowNum)
     then 0 else 1 end as isLeaf
  from CloneIsLeaf
Start With ID = 1
connect by prior ID = PrevID;

--method3
select ID,PrevID,
case when LV < Lead(LV) over(order by RowNum)
     then 0 else 1 end as isLeaf
from (select ID,PrevID,Level as LV
        from CloneIsLeaf
      Start With ID = 1
      connect by prior ID = PrevID
      order siblings by ID);

--method4
select ID,PrevID,
case when LV < Lead(LV) over(order by Row_Num)
     then 0 else 1 end as isLeaf
from (select ID,PrevID,LV,RowNum as Row_Num
        from (select ID,PrevID,Level as LV
                from CloneIsLeaf
              Start With ID = 1
              connect by prior ID = PrevID
              order siblings by ID));
on method2 and method3 and method3,
I used that "Hierarchical Queries" is depth-first search (http://en.wikipedia.org/wiki/Depth-first_search)

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm#i2053935

my site :-)
http://www.geocities.jp/oraclesqlpuzzle/10-149.html
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 7 2007
Added on Sep 3 2007
2 comments
6,078 views