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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Hierarchy Query to get parent nodes?

810903Nov 11 2010 — edited Nov 13 2010
Hi Everyone,

I want to write a hierarchy query which should give me the path starting from given node to its parents(Grand parents). below is the sample data and the output what i am expecting. and also the output what i am getting right now from my query.

CREATE TABLE RELATION (PARENT VARCHAR2(5),CHILD VARCHAR2(5) PRIMARY KEY);

--Data for the tree which starts from the root 'A'
Insert into RELATION (PARENT, CHILD) Values (NULL,'A');
Insert into RELATION (PARENT, CHILD) Values ('A', 'B');
Insert into RELATION (PARENT, CHILD) Values ('A', 'C');
Insert into RELATION (PARENT, CHILD) Values ('B', 'D');
Insert into RELATION (PARENT, CHILD) Values ('B', 'E');
Insert into RELATION (PARENT, CHILD) Values ('D', 'F');
Insert into RELATION (PARENT, CHILD) Values ('C', 'G');

--Data for the tree which starts from the root 'H'
Insert into RELATION (PARENT, CHILD) Values (NULL,'H');
Insert into RELATION (PARENT, CHILD) Values ('H', 'I');
Insert into RELATION (PARENT, CHILD) Values ('H', 'J');

Expected Output by passing values as 'F' which gives the path from bottom to up.

A<-B<-D<-F

My Query:

SELECT substr(sys_connect_by_path(child,'<-'),3)
FROM relation
WHERE connect_by_isleaf = 1
START WITH child = 'F'
CONNECT BY PRIOR parent = child
ORDER BY child;

Output of my query:

F<-D<-B<-A

I am getting the output in reverse order. i can use the reverse string function to reverse the string but the problem is the node can also contain the values like 'AC' 'BA'.. in future.

Can anyone please help me in getting the correct output.

Thank you in advance.

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 11 2010
Added on Nov 11 2010
6 comments
52,319 views