RE: question on hierarchical query

you can use the NOCYCLE option for that purpose
(assuming you are on the right Oracke release)
 
Kind regards,
Lex.
 
-------------------------------
visit http://www.naturaljoin.nl <http://www.naturaljoin.nl> 
-------------------------------
skype me <callto://lexdehaan> 


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of susan lam
Sent: Friday, October 15, 2004 17:36
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: question on hierarchical query


Hi,
thanks for your response. I forgot to include an
important piece of information. 

it's possible that b is related back to a. For eg if
A=100, B=1 and A=1, B=100. If that is the case, I will
get a "ORA-01436: CONNECT BY loop in user data".

What would be the best way of handling that? I
probably need some kind of routine to move the self
looping rows to another table, run the hierarchical
query to output records without the self looping rows
+ display the self looping rows that I initially
moved. Seems like a lot of steps especially if the
table is huge. Is there a better alternative?

thanks.

susan 

SQL> select * from hier;
         A          B
---------- ----------
        99          7
       100          1
       100          2
         1          2
         2          3
         4          8
       200          6
         2          1  <--- recursive 
         1        100  <--- recursive 
 
9 rows selected.

Output should be:
100     1
1       2
2       3
100     2
200     6
2       1
1     100

--- "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
wrote:

> 
> Hi,
> 
> This should 
--- "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
wrote:

> 
> Hi,
> 
> This should satisfy your requirements:
> 
> select distinct * from hier
> connect by prior b = a
> start with a >= 100
> 
> 
> HTH,
> 
> Alan Davey
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
> susan lam
> Sent: Friday, October 15, 2004 8:30 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: question on hierarchical query
> 
> hi,
> I'm having trouble constructing a hierarchical
> query.
> Can someone help.
> 
> SQL> select * from hier;
>          A          B
> ---------- ----------
>         99          7
>        100          1
>        100          2
>          1          2
>          2          3
>        4          8
>        200          6
> 
> 7 rows selected.
> 
> 
> The order of the output is not important. The output
> can be:
> 100   1
> 1     2
> 2     3
> 100   2
> 2     3      -> should not display this duplicate
> 200   6
> 
> OR
> 
> 100   1
> 100   2
> 1     2
> 2     3
> 2     3      -> should not display this duplicate
> 200   6
> 
> We will start with a number. In this case, 100. If
> a=100, get the corresponding value of b and match
> that
> back to a and get the next corresponding value of b
> until b!=a. After the first iteration, now get the
> next value of a>=100 and process it in the same
> manner.
> Duplicate records should only be displayed once, in
> this case, 2 3 should only be displayed once. 
> 
> Is it possible to achieve the requirements in a
> single
> query?
> 
> thanks.
> 
> susan
> 
> 
>       
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 
> "This information in this e-mail is intended solely
> for the addressee and may contain information
> which is confidential or privileged.  Access to this
>  e-mail by anyone else is unauthorized.  If you
> are not the intended recipient, or believe that
> you have received this communication in error,
> please do not print, copy, retransmit,
> disseminate, or otherwise use the information.
> Also, please notify the sender that you have
> received this e-mail in error, and delete the
> copy you received."
> 



                

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l

Other related posts: