Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

multiple columns in connect by prior

user10403630Nov 28 2012 — edited Jul 16 2013
Hi,

I have data something like below

SKU:
--------
ITEM LOC PARENT_ITEM PARENT_LOC
------------------------------------------------------------------------------------------------------------
NULL NULL A 001
A 001 NULL NULL
A 001 NULL NULL
NULL NULL D 002
D 002 NULL NULL
D 002 NULL NULL

And I need output like this

ITEM LOC PARENT_ITEM PARENT_LOC
------------------------------------------------------------------------------------------------------------
NULL NULL A 001
B 001 A 001
C 001 A 001
NULL NULL D 002
E 002 D 002
F 002 D 002


And I tried it with connect by prior.

Select CONNECT_BY_ROOT as item,loc,parent_item,parent_loc FROM
sku
start with parent_item is null and parent_loc is null
connect by prior parent_item = item and parent_loc = loc

But I am getting only parent record not others.
ITEM LOC PARENT_ITEM PARENT_LOC
------------------------------------------------------------------------------------------------------------
A NULL A 001
D NULL D 002


I should apply CONNECT_BY_ROOT and connect by PRIOR on set (like item at loc).
Please help..

Thanks...
This post has been answered by BluShadow on Nov 28 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 13 2013
Added on Nov 28 2012
11 comments
5,333 views