Re: Hierarchical Query! Stoping at a particular value.

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 - 15:24:04 CDT