Home » SQL & PL/SQL » SQL & PL/SQL » connect by loop in user data
connect by loop in user data [message #261143] Wed, 22 August 2007 00:37 Go to next message
ramabhupalrr
Messages: 69
Registered: July 2005
Location: India
Member

Hi All ,

i am get the error "connect by loop in user data".

Presently i am working oracle 9i.

My Table Data is look like this.
table_name:cockpit_data

cockpit_id hrrchy_id parent_id
1 1
1 2 1
1 3 2
1 4
1 5 4
2 8
2 9 8
2 10 9

i have written the query like.

select * from cockpit_data where cockpit_id=1
start with hrrchy_id=1
connect by prior hrrchy_id=parent_id

if write the same query

select * from (select * from cockpit_data where cockpit_id=1)
start with hrrchy_id=1
connect by prior hrrchy_id=parent_id

it is working fine.

Please let me know solution for this.

Note: connect by nocycle prior hrrchy_id=parent_id
but it is not working it is giving the error invalid relational operator


Thanks,
Ram
Re: connect by loop in user data [message #261146 is a reply to message #261143] Wed, 22 August 2007 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then,
In your first query "where" is applied before "connect by", in the second one it is the opposite.
Results (and meanings) are different.

"nocycle" is a feature introduced in 10g.

Regards
Michel
Re: connect by loop in user data [message #261176 is a reply to message #261143] Wed, 22 August 2007 01:50 Go to previous messageGo to next message
ramabhupalrr
Messages: 69
Registered: July 2005
Location: India
Member

Hi ,

Thanks You for the response.
But i am expecting the following output from the above query without using inline view and i heard the that inlinve views not good at performance.
cockpit_id  hrrchy_id parent_id
1             1       
1             2          1
1             3          2


is the hierarchy codition runs before where clause?

Thank you,
Ram.


[Updated on: Wed, 22 August 2007 02:00] by Moderator

Report message to a moderator

Re: connect by loop in user data [message #261184 is a reply to message #261176] Wed, 22 August 2007 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You only want items of cockpit_id=1 then add this condition in the connect by clause.

Regards
Michel
Re: connect by loop in user data [message #261392 is a reply to message #261143] Wed, 22 August 2007 08:24 Go to previous messageGo to next message
ramabhupalrr
Messages: 69
Registered: July 2005
Location: India
Member

Hi Michel,

Thank You.Yes it is working for me.
Re: connect by loop in user data [message #261395 is a reply to message #261392] Wed, 22 August 2007 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post the solution for other readers.

Regards
Michel
Re: connect by loop in user data [message #261522 is a reply to message #261143] Wed, 22 August 2007 23:09 Go to previous message
ramabhupalrr
Messages: 69
Registered: July 2005
Location: India
Member

Hi Michel,

thank you for your support.

This is my work on connect by.

SQL> select * from cockpit_test;

COCKPIT_ID  HRRCHY_ID  PARENT_ID
---------- ---------- ----------
         1          1
         1          2          1
         1          3          2
         1          4
         1          5          4
         2          6          1
         2          3          2
         2          1

8 rows selected.
SQL> select * from cockpit_test 
start with hrrchy_id=1 connect by prior hrrchy_id=parent_id and cockpit_id=1;

COCKPIT_ID  HRRCHY_ID  PARENT_ID
---------- ---------- ----------
         1          1
         1          2          1
         1          3          2
         2          1
         1          2          1
         1          3          2

SQL> select * from cockpit_test 
start with hrrchy_id=1 and cockpit_id=1 connect by prior hrrchy_id=parent_id and cockpit_id=1;

COCKPIT_ID  HRRCHY_ID  PARENT_ID
---------- ---------- ----------
         1          1
         1          2          1
         1          3          2

SQL>


Thanks,
Ram.

[Updated on: Thu, 23 August 2007 01:09] by Moderator

Report message to a moderator

Previous Topic: SQL Help
Next Topic: To find the number of occurences of a string in a clob
Goto Forum:
  


Current Time: Tue Dec 06 02:55:46 CST 2016

Total time taken to generate the page: 0.11045 seconds