OraFAQ Forum: SQL & PL/SQL » connect by loop in user data

Home » SQL & PL/SQL » SQL & PL/SQL » connect by loop in user data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

connect by loop in user data [message #261143]

Wed, 22 August 2007 00:37

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

Report message to a moderator

Re: connect by loop in user data [message #261146 is a reply to message #261143]

Wed, 22 August 2007 00:53

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: connect by loop in user data [message #261176 is a reply to message #261143]

Wed, 22 August 2007 01:50

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

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You only want items of cockpit_id=1 then add this condition in the connect by clause.

Regards
Michel

Report message to a moderator

Re: connect by loop in user data [message #261392 is a reply to message #261143]

Wed, 22 August 2007 08:24

ramabhupalrr
Messages: 69
Registered: July 2005
Location: India

Member

Hi Michel,

Thank You.Yes it is working for me.

Report message to a moderator

Re: connect by loop in user data [message #261395 is a reply to message #261392]

Wed, 22 August 2007 08:52

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please post the solution for other readers.

Regards
Michel

Report message to a moderator

Re: connect by loop in user data [message #261522 is a reply to message #261143]

Wed, 22 August 2007 23:09

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 27 18:03:30 CDT 2025