Re: HELP: recursive query

From: Alton Ayers <altona_at_ditw.com>
Date: Thu, 13 May 1999 08:45:38 -0400
Message-ID: <373AC972.2744AE17@ditw.com>

Here's a refined query which will return only the row you desire:

SELECT keyfield, varname, varvalue
  FROM MyTable
 WHERE level = (SELECT MAX(lv)

                  FROM (SELECT level lv
                          FROM MyTable
                          START WITH varname LIKE '%PP' AND keyfield = 'ABC'
                          CONNECT BY PRIOR varvalue = varname))

  START WITH varname LIKE '%PP' AND keyfield = 'ABC'   CONNECT BY PRIOR varvalue = varname
/

Alton Ayers wrote:

> The following will return you what you need with the ultimate result coming
> first. If you are using a cursor, just fetch the first row and that's it.
>
> SELECT level, keyfield, varname, varvalue
> FROM MyTable
> START WITH varname LIKE '%PP' AND keyfield = 'ABC'
> CONNECT BY PRIOR varvalue = varname
> ORDER BY level DESC
> /
>
> Hope this helps.
>
> Alton
>
> >
Received on Thu May 13 1999 - 07:45:38 CDT