Re: Help with "Connect By"

From: Chris Ellis <cellis_at_iol.ie>
Date: 1997/05/26
Message-ID: <338968CF.8F8@iol.ie>#1/1

Alan Taylor wrote:
>
> I need to traverse backwards through a tree-style table and thought that
> Connect By would be the obvious method, but am having considerable
> difficulty in getting it to work. Can anyone help?
>
> The table has the following structure:
>
> create table TEST (
> Node_Id Number(10) not null,
> Section_Id Number(10) not null,
> Parent_Id Number(10) not null,
> Description VarChar2(200) not null
> )
> constraint Test_PK primary key (Node_Id, Section_Id)
> /
>
> The table contains data along the lines of:
>
> (snip data)
>
> The query that I'm trying to execute needs to extract the tree structure
> leading to a specified item, i.e. find everything LIKE "FA%" ...
>
> Buildings
> Commercial
> Farm
> Factory
>
> ...or everything LIKE "Bedroom%" ...
>
> Buildings
> Residential
> Apartment
> 1 Bedroom
> 2 Bedroom
> 3 Bedroom
> House
> 1 Bedroom
> 2 Bedroom
> 3 Bedroom
>
> Finding the required item is OK, but the problem that I'm having is
> tracing back through the tree to find all the parent items leading down
> to it (Bearing in mind that the located item could occur at any level
> within the tree).
> (snip)

Tricky.

1. Your Root nodes must have a parent_id which is *not* a section_id, otherwise you get a "CONNECT BY loop" error on execution. (I suggest using a parent_id value of null for section_id = 0, since this allows you to define a referential foreign key constraint on parent_id. Just make sure that you use "where section_id = 0" rather than "where parent_id is null" to start top-down searches)
2. You are structuring your query as a "top-down" query, but your "where" clause refers to the "lowest" rows, so you need to check each row returned to ensure that there is a "lower" row in the tree which has the value you want.

The following will work. Just make sure you have indexes on (section_id,node_id) and on (parent_id,node_id)!

select lpad(' ',2*level,' ')||description "Desc" from test T1
where exists
  (select null from test T2
   where T2.description like '%Bedroom%'

   start with (T2.node_id = T1.node_id	-- start from current row
           and T2.section_id = T1.section_id)

   connect by T2.node_id = prior T2.node_id    and T2.parent_id = prior T2.section_id   )
start with section_id = 0 -- all "root" nodes connect by node_id = prior node_id

       and parent_id = prior section_id
/

Hope this helps.

Chrysalis. Received on Mon May 26 1997 - 00:00:00 CDT