Re: HELP: recursive query

This worked when I tested it on the data you provided:

select a.KEYFIELD,b.VARNAME, a.VARVALUE from MyTable a, MyTable b
where (a.KEYFIELD,a.COUNTER) in (
select KEYFIELD,max(COUNTER)
from t1
start with VARNAME like '%_pp'
connect by VARNAME=prior VARVALUE
group by KEYFIELD)
and a.KEYFIELD=b.KEYFIELD
and b.VARNAME like '%_pp'

Result was:

abc        $d123_pp   pptvh_486
def        $l472_pp nhg23_77x

Note that it relies on the final value being held in the row with the highest counter value.
Of course it is not optimised - just cobbled together.

If you have any further questions regarding this feel free to email me.

Geoff

Michelle Watson wrote:

> 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
>

> <snipped due to included text limitation>
Received on Wed May 12 1999 - 22:28:42 CDT