OraFAQ Forum: SQL & PL/SQL » Recursive query

Home » SQL & PL/SQL » SQL & PL/SQL » Recursive query (oracle11.2.0.3)

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

Recursive query [message #643140]

Wed, 30 September 2015 03:46

vasudevaviswa
Messages: 53
Registered: August 2015
Location: US

Member

Hi All,

Please find here.

A1, , B2, C3are PARENTS.
A1 has Children i.e D4, E5
D4 has GrandChildren i.e F6,G7
E5 has Grand Children i.eH8

I have existed data asfollows.

ID            Name
-------------------
| a1       | 1  | 
| b2       | 2  | 
| c3       | 3  |
| a1.d4    | 4  | 
| a1.e5    | 5  | 
| a1.d4.f6 | 6  | 
| a1.d4.g7 | 7  | 
| a1.e5.h8 | 8  | 
| a2.i9    | 9  | 
| a2.i9.j10| 10 |
etc..
etc...

Now I would like to write a query to retrieve Parent levels for

Required O/P :

| Name     | ID | PARENT_ID |
-----------------------------
| a1       | 1  | null      |
| b2       | 2  | null      |
| c3       | 3  | null      |
| a1.d4    | 4  | 1         |
| a1.e5    | 5  | 1         |
| a1.d4.f6 | 6  | 4         |
| a1.d4.g7 | 7  | 4         |
| a1.e5.h8 | 8  | 5         |
| a2.i9    | 9  | 2         |
| a2.i9.j10| 10 | 9         |
...
...

I have here around 500k data..

Can anyone advise query forDynamic data.

Thanks,
Vasudev

Lalit : Corrected code tags

[Updated on: Wed, 30 September 2015 04:28] by Moderator

Report message to a moderator

Re: Recursive query [message #643141 is a reply to message #643140]

Wed, 30 September 2015 04:29

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Please post a working test case. And please read and follow the OraFAQ Forum Guide and How to use [code] tags.

Report message to a moderator

Previous Topic:	No numbers on Imbalance and Variance...
Next Topic:	MERGE and SEQUENCE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 03:15:36 CDT 2024