RE: question on hierarchical query

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Fri, 15 Oct 2004 19:09:17 +0200
Message-ID: <JFEEIGBIDOCCDALDIPLNKEAICKAA.lex.de.haan@naturaljoin.nl>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of susan lam Sent: Friday, October 15, 2004 17:36
To: oracle-l_at_freelists.org
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_at_encodasystems.com> wrote:

>
> Hi,
>
> This should

--- "Davey, Alan" <Alan.Davey_at_encodasystems.com> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
> susan lam
> Sent: Friday, October 15, 2004 8:30 AM
> To: oracle-l_at_freelists.org
> 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

Received on Fri Oct 15 2004 - 12:04:54 CDT