CONNECT BY - how to get leaves only

From: Kim Ng <kimmng_at_pebbles.uswnvg.com>
Date: 16 Dec 1994 02:04:42 GMT
Message-ID: <3cqsjq$8jd_at_fred.uswnvg.com>

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

---

 (Whatever I say and own are mine, mine and only mine! So, don't you dare   claim they are yours!)
 Kim Ng
 Paradigm Computer Consulting, Inc.
 20611 E Bothell-Everett Hwy SE, Suite 280  Bothell, WA 98012
 U.S.A. Received on Fri Dec 16 1994 - 03:04:42 CET