Re: Hierarchical Query! Stoping at a particular value.

Thanks MS:

But I don't think that will work.

ORA-01473: cannot have subqueries in CONNECT BY clause.

Mine is 8.1.7.4

I have the following two cases worked out.

select * from t1 where level <=
 (
 select min(level) lvl
 from t1
 where crit = 'T'
 start with childkey = 17
 connect by prior parentkey = childkey  )
start with childkey = 17
connect by prior parentkey = childkey
union all
select * from t1
where not exists (
  select null from t1
  where crit = 'T'
  start with childkey = 17
  connect by prior parentkey = childkey   )
and ( crit = 'F' or crit is null)
start with childkey = 17
connect by prior parentkey = childkey  

Method-2: May be the better one:

--works for totals levels less than 9.999E125, if there is no 'T'

select * from t1 where level <=
 (
 select nvl(min(level),9.999E+125) lvl
 from t1
 where crit = 'T'
 start with childkey = 17
 connect by prior parentkey = childkey  )
start with childkey = 17
connect by prior parentkey = childkey

Thanks M. Hashim for hinting me to use the maximum number technique(hack?).

qcom_at_attbi.com (MS) wrote in message news:<a2b6d46b.0208291201.2d72c38f_at_posting.google.com>...
> 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 Fri Aug 30 2002 - 15:42:53 CDT