Re: ORA-01437: cannot have join with CONNECT BY

"Jay" <no_at_spam.com> wrote in message news:<bjno67$m9h$1_at_msunews.cl.msu.edu>...
> Can I use "connect by" in view?
> The view is created from joining 2 tables.
>
>
> I was trying
>
> SELECT col1
> FROM view1
> START WITH col1= 45034
> CONNECT BY PRIOR col2= col1;
>
> and getting
>
> ORA-01437: cannot have join with CONNECT BY
>

No, you can't. At least not in 8i. Dunno about 10, though. What you can do is use the connect by in the original tables and join the result in a view.
Kinda like:

select ...
from ( select ... from TA where ... connect by ... start with ...) t1,

     ( select ... from TB where ... connect by ... start with ...) t2 where t1.col1 = t2.col1;

HTH Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Wed Sep 10 2003 - 16:34:59 CDT