HELP: recursive query

I have a table as follows:

     SQL> desc MyTable
      Name                            Null?    Type
      ------------------------------- -------- ----
      KEYFIELD                        NOT NULL VARCHAR2(12)
      COUNTER                         NOT NULL NUMBER(10)
      VARNAME                                  VARCHAR2(15)
      VARVALUE                                 VARCHAR2(32)

The data is such that the varName field contains the name of a variable (prepended with a '$'), and the varValue is the value of the variable, which itself can be a variable.

Example data:

     KEYFIELD COUNTER  VARNAME    VARVALUE
     -------- -------  ---------- ----------
     ABC            0  $D123_PP   $D123_OIN
     ABC            1  $D123_OIN  $GB98_FAR
     ABC            2  $GB98_FAR  PPTVH_486
     DEF            0  $J472_X    $LKE30_X
     DEF            1  $J472_PP   $LKE30_DWF
     DEF            2  $LKE30_DWF $TR_UIF_3
     DEF            3  $TR_UIF_3  NHG23_77X

Note that the varValue which is a variable for one varName is the varName for another row.

The varName which has the tag '%_PP' is the main search criteria for each keyField. Is it possible to get the final varValue that is not a variable in a single query, rather than doing a recursive search? I.e. instead of

     select varValue FROM MyTable WHERE keyField = 'ABC' AND
     varName LIKE '%_PP';

     KEYFIELD  VARNAME   VARVALUE


     --------- --------- ---------


     ABC       $D123_PP  $D123_OIN

     select varValue FROM MyTable WHERE keyField = 'ABC' AND
     varName = '$D123_OIN';

     KEYFIELD  VARNAME   VARVALUE


     --------- --------- ---------


     ABC       $D123_OIN $GB98_FAR

     select varValue FROM MyTable WHERE keyField = 'ABC' AND
     varName = '$GB98_FAR';

     KEYFIELD  VARNAME   VARVALUE


     --------- --------- ---------


     ABC       $GB98_FAR PPTVH_486

is there a single query, like...?

     SELECT keyValue, varValue
     FROM Mytable
     WHERE keyField = 'ABC'
     AND varName like '%_PP'
     AND <varValue is ultimate value>;

     KEYFIELD  VARNAME   VARVALUE


     --------- --------- ---------


     ABC       $D123_PP  PPTVH_486

Or ...

     SELECT keyValue, varValue
     FROM Mytable
     WHERE varName like '%_PP'
     AND <varValue is ultimate value for keyValue>;

     KEYFIELD  VARNAME   VARVALUE


     --------- --------- ---------


     ABC       $D123_PP  PPTVH_486
     DEF       $J472_PP  NHG23_77X

Thanks,
Michelle Received on Wed May 12 1999 - 10:03:58 CDT