>> When I've first encountered this, i solved it immediately and then
>> tested in local database and found out that if iuse field from outer
>> select or conditions in subselects - the results aren't predictable
>> anymore.

>Are you sure, Bogdan? Helen once told me never to think of parameters
>as variables, but as placeholders. Your query in question is

I'm quite positive about it.

>> SELECT VALID_FROM, PRICE FROM MATERIALCENA
>> WHERE MATERIALid = :IMATERIALID AND VALID_FROM =
>> (select max(MC.VALID_FROM from materialcena mc where mc.materialid =
>> :imaterialid and mc.VALID_FROM <= 'today')
>> INTO :VALID_FROM, :PRICE;

>and here you have two parameters which you have given the same name.
>I'd say that you should change your select to either

>SELECT M.VALID_FROM, M.PRICE FROM M.MATERIALCENA
>WHERE M.MATERIALid = :IMATERIALID AND M.VALID_FROM =
>(select max(MC.VALID_FROM from materialcena mc where mc.materialid =
>:othername and mc.VALID_FROM <= 'today')
>INTO :VALID_FROM, :PRICE;

The parameter iMaterialID is input parameter and should be the same in both
places.
----
What upsets me is that procedure runs ok for months and than suddenly
changes behaviour.
After restaring server it runs ok again.

One looses confidence when such things happen.

As Alexander said, maybe it is because of dialect 1.


Thanks to all
Bogdan