Re: Hierarchical Query! Stoping at a particular value.

how about something like this:

select *
from t
start with childrec = <whatever>
 connect by prior parentrec = childrec
and level <= (

     select min(t_level)*-1  --Gets the level for 'T'
       from (
             select decode(crit,'T',1evel*-1,level) t_level
               from t
              start with childrec = <whatever>
               connect by prior parentrec  = childrec 
             ) )

-Madhu S

gspk_at_yahoo.com (Prince Kumar) wrote in message news:<629275ba.0208282129.16813d71_at_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 - 15:01:41 CDT