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

Try adding your missing bay with a UNION. Making one or two assumptions about your table and how you want your unattached row to appear, this would give :

select EQUIP_HOLDER_ID, PARENT_ID , LEVEL         )
from XBN_EQUIP_HOLDER_T                           ) As
start with TYPE=0                                 ) before
connect by PARENT_ID = prior EQUIP_HOLDER_ID      )
UNION ALL
select EQUIP_HOLDER_ID, null, 0                   )
from XBN_EQUIP_HOLDER_T                           ) Change this if
where type = 0                                    ) my assumptions are wrong
and parent_id is null                             )

Simon Received on Fri Jun 09 1995 - 00:00:00 CEST