Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Hierarchical Query! Stoping at a particular value.

Re: Hierarchical Query! Stoping at a particular value.

From: Prince Kumar <gspk_at_yahoo.com>
Date: 28 Aug 2002 21:45:20 -0700
Message-ID: <629275ba.0208282045.48b6ba2b@posting.google.com>


Mr. Sybrand!

I have already read the manual and couldn't find the best solution. I would have appreciated if you have given a solution and then commented.

Anyway, my question is not on just the hierachical query. I want to stop when a certain condition is met going from bottom to top.

This is the query I have. I still have to verify the validity of the query and the response time.

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 parentrey = childrec
	union all	-->I do not need to run this portion if the above returns a
value
	select max(level) lvl
	from t1
	where crit = 'F' or crit is null
	start with childrec=?
	connect by prior parentrey = childrec
	)

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

Regards,
Prince.

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 Wed Aug 28 2002 - 23:45:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US