CONNECT BY Help

From: <yachoudary_at_yahoo.com>
Date: Tue, 02 Jan 2001 17:46:28 GMT
Message-ID: <92t45h$7g1$1@nnrp1.deja.com>

Hi,

I am facing a problem with Connect BY query and I appreciate any help in this regard.

I will try to explain my problem with sample scripts.

<pre>
SVRMGR> create table testtab (name char(10), source int, target int); Statement processed.
SVRMGR>
SVRMGR> insert into testtab values ('aaaa', 0, 1); 1 row processed.
SVRMGR> insert into testtab values ('bbbb', 1, NULL); 1 row processed.
SVRMGR> insert into testtab values ('cccc', 1, NULL); 1 row processed.
SVRMGR>
SVRMGR> select * from testtab

    2> start with name = 'aaaa'
    3> connect by prior target = source; NAME SOURCE TARGET
---------- ---------- ----------

aaaa                0          1
bbbb                1
cccc                1

3 rows selected.
SVRMGR>
SVRMGR> select * from testtab

    2> start with name = 'bbbb'
    3> connect by prior target = source; NAME SOURCE TARGET
---------- ---------- ----------

bbbb                1

1 row selected.
SVRMGR>
SVRMGR> select * from testtab

    2> start with name = 'cccc'
    3> connect by prior target = source; NAME SOURCE TARGET
---------- ---------- ----------

cccc                1

## The above queries are good, if I know the 'start with' predicate.
## What if I want to display all such hierarcies in the table as
## follows :

STARTWITHNAME NAME SOURCE TARGET
------------- ------ ------ ------

aaaa          aaaa        0      1
aaaa          bbbb        1
aaaa          cccc        1
bbbb          bbbb        1
cccc          cccc        1

I tried the following query to achive this. But for some reason, this displays 9 rows and the rows listed are not the same as I expected.

SVRMGR> select t2.name startname, t1.name, t1.source, t1.target
     2>  from testtab t1, testtab t2
     3>  start with t1.name = t2.name
     4>  connect by prior t1.target = t1.source order by 1;
STARTNAME  NAME      SOURCE    TARGET
---------- ---------- ---------- ----------
aaaa      aaaa                0          1
aaaa      bbbb                1
aaaa      cccc                1
bbbb      bbbb                1
bbbb      bbbb                1
bbbb      cccc                1
cccc      bbbb                1
cccc      cccc                1
cccc      cccc                1

9 rows selected.
</pre>

 Thanks in advance,
 -Choudary

Sent via Deja.com
http://www.deja.com/ Received on Tue Jan 02 2001 - 11:46:28 CST