SQL & PL/SQL

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>

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.

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'.

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.

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.

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?

Thankyou Solomon. its working.

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.

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

Thankyou all of you. its such a good forum.... :)

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

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.