Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Hierarchical Query! Stoping at a particular value.
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 )
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