Home » SQL & PL/SQL » SQL & PL/SQL » CONNECT BY - Having root node displayed in every row
CONNECT BY - Having root node displayed in every row [message #189247] Wed, 23 August 2006 16:38 Go to next message
pa1sas3
Messages: 27
Registered: June 2005
Junior Member
I have a query like this:

select parent_id,child_id,child_desc
from tab1
start with parent_id in (SELECT root_parent_id in tab2)
connect by prior child_id = parent_id

This give the set of rows. Now, I want the parent_id in the 'START WITH' clause in each and every row of the result set as root_parent_id.
So, the result set should contain:

root_parent_id, parent_id, child_id,child_desc.

I can do it using a cursor.....but am lokking to do that in one single query....

Any help?

Pa1.
Re: CONNECT BY - Having root node displayed in every row [message #189251 is a reply to message #189247] Wed, 23 August 2006 16:50 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
If you are on 10g, you can use CONNECT_BY_ROOT.

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm#i2069380
Re: CONNECT BY - Having root node displayed in every row [message #189253 is a reply to message #189251] Wed, 23 August 2006 17:05 Go to previous messageGo to next message
pa1sas3
Messages: 27
Registered: June 2005
Junior Member

Thanks for the lead.
We are using Oracle 9.2.0.4. Is there a way out?
I am trying SYS_CONNECT_BY_PATH (use substr and get the root if I can use it in version 9.2)
Pa1
Re: CONNECT BY - Having root node displayed in every row [message #189255 is a reply to message #189253] Wed, 23 August 2006 17:19 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
In 9i, you can use sys_connect_by_path but you have to do some parsing yourself.
Previous Topic: Explain SQL and using an Index
Next Topic: variable declaration
Goto Forum:
  


Current Time: Thu Dec 08 19:59:43 CST 2016

Total time taken to generate the page: 0.10147 seconds