Re: CONNECT BY - how to get leaves only
Date: 19 Dec 1994 15:02:32 GMT
Message-ID: <3d47a8$40i_at_redwood.cs.scarolina.edu>
kimmng_at_pebbles.uswnvg.com (Kim Ng) writes:
>Hi everyone,
>I hope somebody out there can help me with the following problem.
>(I am using SQLFORMS30 on ORACLE version 7.0.)
>Basically, I need to get the "leaves" of a tree in a "connect by"
>statement (ie the last node) in default list of values. I do not want
>any intermediate nodes to come up. Also, since the "select" will be
>used in a default "list of values" box, it will have to be a single
>"select" statement. No PL/SQL statement can be used. Just plain basic
>"select". Also, I do not know how deep the levels can be and the branches
>can have different levels.
>The table and the content looks like this :
> A B
>---------- ----------
> NULL 1
> 1 2
> 2 3
> 3 4
> NULL 11
> 11 12
> NULL 21
> NULL 31
>The tree structure looks like this:
> NULL
> |
> ------------------------
> | | | |
> 1 11 21 31
> | |
> 2 12
> |
> 3
> |
> 4
>The desired answer will be (in the list of values box):
> 4
> 12
> 21
> 31
>I have come up with a "select" statement. However, it doesn't work!
>For some reason, the sub-query causes the "select" to return only
>the branch with only 1 node (ie: 21 and 31). Here's the query:
>select t1.a, t1.b, level
> from x t1
> where level =
> (select max (level)
> from x t2
> where t2.b is not null
> connect by prior t2.b = t2.a
^ ^
> start with t2.b = t1.b)
> connect by prior t1.b = t1.a
^ ^
shouldn't this be reversed?
> start with t1.a is null
>I wonder what's wrong with the query and how it should be. It works fine
>if I hardcoded the level but, as I mentioned earlier, I can't do that in
>the actual code.
>Any suggestion, help or hint will be greatly appreciated.
Instead of selecting a node at the max level for that branch (a leaf), try selecting all nodes that aren't in the middle! (Same thing, right?):
select t1.a, t1.b, level
from x t1
where t1.b not in
(select prior a
from x
connect by prior a = b
start with a is null)
connect by prior a = b
start with a is null
L. Scott Johnson Received on Mon Dec 19 1994 - 16:02:32 CET