Re: Hierarchical Query! Stoping at a particular value.

From: Prince Kumar <gspk_at_yahoo.com>
Date: 28 Aug 2002 22:29:31 -0700
Message-ID: <629275ba.0208282129.16813d71@posting.google.com>

I guess, this is the corrected one.

select * from t1 where level <=
(select min(lvl) minlvl from

	(
	select min(level) lvl
	from t1
	where crit = 'T'
	start with childrec = ?
	connect by prior parentrec  = childrec 
	union  --> need only if the above doesn't returns a value
	select max(level) lvl
	from t1
	where ( crit = 'F' or crit is null)
	and not exists ( select  null from t1
		where crit = 'T'
		start with childrec = ?
		connect by prior parentrec  = childrec )
	start with childrec = ?
	connect by prior parentrec  = childrec 
	)

)
start with childrec = ?
connect by prior parentrec = childrec ;

Sybrand Bakker <postbus_at_sybrandb.demon-verwijderdit.nl> wrote in message news:<o9cqmuo79rdivt97jhqf9pjc1cuvkna8s1_at_4ax.com>...
> On 28 Aug 2002 13:02:16 -0700, gspk_at_yahoo.com (Prince Kumar) wrote:
>
> >I want to get the list of all values starting from the child to the
> >parent until a certain condition is met.
> >
> >How do I write a efficient SQL for this?
> >
> >Ex.
> >------
> >
> >select * from my t ;
> >
> >Key PKey Crit
> >---- ----- -----
> >1 null null
> >
> >4 3 T
> >
> >7 4 F
> >
> >8 7 T
> >
> >11 8 null
> >
> >17 11 null
> >
> >16 null null
> >
> >19 14 null
> >
> >
> >
> >I want to start at a particular "Key" and find all its parent till the
> >first "Crit" = 'T'. if "Crit" is null keep on finding the parent until
> >the root level is met.
> >
> >Ex,
> >
> >if I start with Key = 9, I should get
> >---------------------------------------
> >19 14 null --> no more parents
> >
> >if I start with Key = 17, I should get
> >---------------------------------------
> >17 11 null
> >11 8 null
> >8 7 T --> Crit "T" is met (do not go further)
> >
> >if I start with Key = 4, I should get
> >---------------------------------------
> >4 3 T --> Crit "T" is met
> >
> >Thanks,
>
>
> read up on CONNECT BY on the sql reference, or should we directly
> reply to your teacher?
>
> Hth
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Thu Aug 29 2002 - 00:29:31 CDT