Home » SQL & PL/SQL » SQL & PL/SQL » Connect By is looping (oracle 9i, WinXP)
Connect By is looping [message #350558] Thu, 25 September 2008 07:19 Go to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Hey!

I have a query including a 'Start With ... Connect By A = B...' statement.
For some data, the following error is rasing : ORA-01436: CONNECT BY loop in user data.
After checking the data, I found that the value of A and the value of B are the same which is causing a loop and producing the error above.

Is there something (a property of CONNECT BY for example or something else) that handles this case?

Rajy
Re: Connect By is looping [message #350559 is a reply to message #350558] Thu, 25 September 2008 07:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
CONNECT BY NOCYCLE.....
Re: Connect By is looping [message #350560 is a reply to message #350558] Thu, 25 September 2008 07:39 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Dear,

I tried to use the NOCYCLE, but the following error is raised too: ORA-00920: Invalid relational operator.

Is that property supported in 9i? or in 10g and later?

Rajy

[Updated on: Thu, 25 September 2008 07:40]

Report message to a moderator

Re: Connect By is looping [message #350561 is a reply to message #350560] Thu, 25 September 2008 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NOCYCLE was introduced in 10g.
In 9i, you have to write your own PL/SQL procedure.

Regards
Michel
Re: Connect By is looping [message #350566 is a reply to message #350561] Thu, 25 September 2008 07:55 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try:

CONNECT BY PRIOR A = B
AND PRIOR A <> PRIOR B


Ross Leishman
Re: Connect By is looping [message #350569 is a reply to message #350558] Thu, 25 September 2008 08:01 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Thank you very much, it works!!

Rajy
Re: Connect By is looping [message #356245 is a reply to message #350566] Thu, 30 October 2008 01:31 Go to previous messageGo to next message
nithiraj
Messages: 1
Registered: October 2008
Junior Member
Hi Ross Leishman,

I had a simular issue and your solution helped.

Thanks
Regards!
Nithiraj.
Re: Connect By is looping [message #356279 is a reply to message #350560] Thu, 30 October 2008 02:59 Go to previous message
_jum
Messages: 508
Registered: February 2008
Senior Member
If there is a true loop You can use these algorithm:
http://laurentschneider.com/wordpress/2008/09/cycling.html
Previous Topic: sum expression oddity (merged 2)
Next Topic: decode syntax in Stored Procedure
Goto Forum:
  


Current Time: Tue Dec 06 11:54:38 CST 2016

Total time taken to generate the page: 0.11276 seconds