Helen Borrie wrote:

> At 03:12 PM 10/01/2003 +0200, you wrote:
>
>>Hi
>>I am still using FB1 where the problem is.
>>
>>This line of code is in a for select statement
>>
>>select VEHICLE_REGISTRATION_NO from DAR
>> where darno = (select darno from CONVERTTORONUMBER where ronno
>>= :DARNO_RONO) into :VehicleRegistration
>>
>>The problem is the subselect returns the same result 90% percent of the
>>time which makes the select statement return the same result over and
>>over again.
>>
>>If I run this statement seperatly it returns the correct result everytime.
>>
>>The only solution I found is to select the
>>
>>"select darno from CONVERTTORONUMBER where ronno = :DARNO_RONO"
>>
>>into a seperate variable and using the variable for the where statement.
>
>
> It looks like an ambiguity problem, maybe because the main table and the
> subquery table are being (ambiguously) correlated by matching column
> identifiers.
>
> It makes me wonder if CONVERTTORONUMBER is itself a view or a selectable SP
> involving the same table...?
>
> If this theory is worth testing, try this instead:
>
> select d.VEHICLE_REGISTRATION_NO from DAR d
> where d.darno = (select c.darno from CONVERTTORONUMBER d where c.ronno
> = :DARNO_RONO) into :VehicleRegistration
>
> heLen
>

The identifiers did not do the trick. I get the same problem.
CONVERTTORONUMBER is a table.

The main for select statement that I "typed" of earlier is yet another
table so 3 different tables are accessed here.
CONVERTTORONUMBER
DAR
DIARY

A brief explanation.
All process in the db start with a dar number and is then later
converted to a rono. This conversion is stored in CONVERTTORONUMBER.
Sometimes I need data that is stored with the dar key and then I first
need to do a lookup in CONVERTTORONUMBER if I am in the rono state. Hope
this is understandable.

Groete
Gerhardus