RE: question on hierarchical query
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <susanzlam@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 15 Oct 2004 19:09:17 +0200
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
>
>
>
>
> --
> //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."
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
