Re: cannot have subqueries in CONNECT BY clause

Alexander V. Silantiev doodled thusly:

>Thank you.
>
>Alexander.
>

No worries.

The following is what I came up with. Using your table script and some quick data for simply showing the result of the join and its "filtering" of the output rows. Like the original EXISTS did.

You will of course recognize the trick to make the join work: "if Muhamad doesn't go to the Mountain, then the Mountain goes to Muhamad". Old as old can get, but still works!

create table classify(
cid number,
cmaster number,
CONSTRAINT pk_classify PRIMARY KEY(cid) using index , CONSTRAINT fk_classify_master FOREIGN KEY(cmaster) REFERENCES classify ) ;
create table classifyRW (
cid number,
username varchar2(30),
CONSTRAINT pk_classifyRW PRIMARY KEY(cid,username) using index , CONSTRAINT fk_classifyRW_classify FOREIGN KEY(cid) REFERENCES classify ON
DELETE CASCADE
) ;

Now, contents and runtime:

Yes Mastah?>select * from classify;
and suddenly, Zorg gently tapped the Enter key...

       CID CMASTER
---------- ----------

         1 *NULL*
        10          1
        11          1
        12          1
       110         11
       111         11
       112         11

7 rows selected.

Elapsed: 00:00:00.70
Yes Mastah?>select * from classifyrw;
and suddenly, Zorg gently tapped the Enter key...

       CID USERNAME

---------- ------------------------------
         1 one
        10 onezero
        11 oneone
       110 oneonezero
       111 oneoneone
       112 oneonetwo

6 rows selected.

Elapsed: 00:00:00.81
Yes Mastah?>get cby1
  1 select
  2 a.cid,c.username
  3 from (
  4 select b.cid,b.cmaster
  5 from classify b
  6 start with b.cmaster is null
  7 connect by prior b.cid=b.cmaster
  8 )a, classifyrw c
  9* where a.cid = c.cid
Yes Mastah?>/
and suddenly, Zorg gently tapped the Enter key...

       CID USERNAME

---------- ------------------------------
         1 one
        10 onezero
        11 oneone
       110 oneonezero
       111 oneoneone
       112 oneonetwo

6 rows selected.

Elapsed: 00:00:00.81
Yes Mastah?>exit

HTH Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Wed Nov 21 2001 - 04:14:02 CST