Re: sql: hierarchical data - roots with no children

In article <3r71d7$d6p_at_athos.cc.bellcore.com>, bleonard_at_marcel.gamekeeper.bellcore.com (William Leonard) writes:
> I have an sql stmt that selects hierarchical data
> from a table using 'start with/connect by'. It does not
> return root rows that have no children (which I want it
> to do). Any help is appreciated.
>
> sql stmt:
> ---------
> select EQUIP_HOLDER_ID, PARENT_ID , LEVEL
> from XBN_EQUIP_HOLDER_T
> start with TYPE=0
> connect by PARENT_ID = prior EQUIP_HOLDER_ID
>
> Data are in a table, xbn_equip_holder_t. Bays have shelves
> which, in turn, have slots. The data is represented by:
>
> bay: 1 2 3 4
> / \ | |
> shelf: 5 6 7 8
> / | \ / \
> slot: 9 10 11 12 13
>
> ** Bay 2 is not returned in the result.
>

I believe that you have a mistake in your data. Probably, PARENT_ID is NULL rather then 0 in row with EQUIP_HOLDER_ID=2.

With correct data all work Ok against Oracle 7.1.3.

Alexandr Alesinsky
JSV Investor Received on Tue Jun 20 1995 - 00:00:00 CEST