Skip to Main Content
Forums

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 For Full Tree ?

810903Nov 9 2010 — edited Nov 12 2010
Hi Everyone,

I want to write a hierarchy query which should give me the whole path starting from root node to each individual nodes by passing value of any individual tree member. 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(1),CHILD VARCHAR2(1) 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 'C' which gives the whole tree where the node C is present:

A
A->B
A->C
A->B->D
A->B->E
A->C->G
A->B->D->F


My Query:

select
sys_connect_by_path(child,'->') tree
from
relation
--where (parent ='C' or child='C')
start with
parent is null
connect by
prior child = parent
order by tree;

Output of my query:

->A
->A->B
->A->B->D
->A->B->D->F
->A->B->E
->A->C
->A->C->G
->H
->H->I
->H->J

I am not able to add the condition for the query so that i can get only rows of the tree where the nod 'C' is present. i am just getting whole data from the table with all the unwanted trees.

Can anyone please help me in getting the correct output.

Thank you in advance.
This post has been answered by Solomon Yakobson on Nov 9 2010
Jump to Answer

Comments

BluShadow
Perhaps something like this...
SQL> ed
Wrote file afiedt.buf

  1  select tree
  2  from (
  3        select sys_connect_by_path(child,'->') tree
  4              ,length(replace(sys_connect_by_path(case when child = 'C' then '1' else null end,','),',')) as cnt
  5        from relation
  6        --where (parent ='C' or child='C')
  7        connect by prior child = parent
  8        start with parent is null
  9       )
 10* where cnt > 0
SQL> /

TREE
--------------------------------------------------------------------------------------------------------------------
->A->C
->A->C->G

SQL>
Solomon Yakobson
Answer 
select  substr(sys_connect_by_path(child,'->'),3) tree
  from relation
  start with child in (
                       select  child
                          from relation
                          where connect_by_isleaf = 1
                          start with child = 'C'
                          connect by child = prior parent
                       )
  connect by parent = prior child
  order by level,
           tree
/

TREE
------------------------
A
A->B
A->C
A->B->D
A->B->E
A->C->G
A->B->D->F

7 rows selected.

SQL>
SY.
Marked as Answer by 810903 · Sep 27 2020
Frank Kulash
Hi,

You can do that with a Yo-Yo Query , where you do CONNECT BY queries in alternating directions: 
SELECT	SYS_CONNECT_BY_PATH (child, '->')	AS tree
FROM	relation
START WITH	child	IN (
				SELECT	child
				FROM	relation
				WHERE	CONNECT_BY_ISLEAF	= 1
				START WITH	child	= 'C'
				CONNECT BY	child	= PRIOR  parent
      			   )
CONNECT BY	PRIOR  child = parent
ORDER BY	tree
;
Here, we're doing a bottom-up query, starting with the target child ('C'), to find its root ('A').
Then we're doing a top-down query to get all the descendants of that root.
Note that the main query is just what you posted, except for the START WITH clause.
1 person found this helpful
odie_63
Another solution, involving analytics : 
SELECT tree
FROM (
  select sys_connect_by_path(child,'->') tree, 
         max(case when child = 'C' then connect_by_root(child) end) over() root1, 
         connect_by_root(child) root2
  from relation
  start with parent is null
  connect by prior child = parent
)
WHERE root1 = root2
;
Here, all trees are built and then filtered using the root of 'C'.
BluShadow
Ah, seems I misunderstood the requirement. I thought he just wanted branches that contained "C". Now it makes sense. Thought it was an odd requirement.
Solomon Yakobson
odie_63 wrote:
Another solution, involving analytics :
Hierarchical queries and analytic functions do not mix well together in 10g:
SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> SELECT tree
  2  FROM (
  3    select sys_connect_by_path(child,'->') tree, 
  4           max(case when child = 'C' then connect_by_root(child) end) over() root1, 
  5           connect_by_root(child) root2
  6    from relation
  7    start with parent is null
  8    connect by prior child = parent
  9  )
 10  WHERE root1 = root2
 11  ;
  from relation
       *
ERROR at line 6:
ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], []


SQL>
Although it is much better in 11g:
SQL> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> SELECT tree
  2  FROM (
  3    select sys_connect_by_path(child,'->') tree, 
  4           max(case when child = 'C' then connect_by_root(child) end) over() root1, 
  5           connect_by_root(child) root2
  6    from relation
  7    start with parent is null
  8    connect by prior child = parent
  9  )
 10  WHERE root1 = root2
 11  ;

TREE
------------------------------------------------------------------------------------------
->A
->A->B
->A->B->D
->A->B->D->F
->A->B->E
->A->C
->A->C->G

7 rows selected.

SQL>
SY.
odie_63
Solomon Yakobson wrote:
Hierarchical queries and analytic functions do not mix well together in 10g:
Yes, true.

That reminds me of an old thread :
4233188

Does it work better with : 
alter session set "_optimizer_connect_by_cost_based" = false;
in 10g?
810903
Thankyou Solomon. its working.
810903
As i am a beginner, Do you have any document which gives all the details of hierarchy function. If so please post the link or you can send it to me on sartaj123@indiatimes.com.
810903
Thanks again Frank.

As i am a beginner, Do you have any document which gives all the details of hierarchy function. If so please post the link or you can send it to me on sartaj123@indiatimes.com.

Regards,
Sartaj
810903
Thankyou all of you. its such a good forum.... :)
Aketi Jyuuzou
I like recursive with clause and Hierarchical Query B-)

This is Yo-Yo query.
I remember THE GOONIES2 of Nintendo nes :8}
with RELATION(PARENT,CHILD) as(
select NULL,'A' from dual union all
select 'A', 'B' from dual union all
select 'A', 'C' from dual union all
select 'B', 'D' from dual union all
select 'B', 'E' from dual union all
select 'D', 'F' from dual union all
select 'C', 'G' from dual union all
select NULL,'H' from dual union all
select 'H', 'I' from dual union all
select 'H', 'J' from dual),
rec(PARENT,CHILD,path) as(
select PARENT,child,cast(child as varchar2(20))
  from relation
 where connect_by_isleaf = 1
start with child = 'C'
connect by child = prior parent
union all
select b.PARENT,b.child,
a.path  || '->' || b.child
  from rec a,RELATION b
 where a.child = b.parent)
select CHILD,path from rec;

C  PATH
-  ----------
A  A
B  A->B
C  A->C
D  A->B->D
E  A->B->E
G  A->C->G
F  A->B->D->F
Frank Kulash
Hi,

Sorry; I thought I had a link to a tutorial, bu I can't find it now.

I just looked for 
Oracle "CONNECT BY" tutorial
in Google, and found several likely looking things, but none for which I can vouch.

Of course, the SQL Language manual
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries003.htm#i2053935
is an essential reference, but it's a reference, not a thorough introduction.
1 - 13
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 10 2010
Added on Nov 9 2010
13 comments
35,256 views