Hello all.

Here is my scenario:

Informix Driver 3.82.0000 2.81.TC2

--I cannot update this driver, large amount of stations, hard apps,.... --

I've got a select in a odc connection and this select returns me a lot of locked rows.

So I must run this select in dirty read level and I cannot modify any value of the connection so the isolation leves has to be set inside the select.

The select syntax is like this:

{SET ISOLATION TO DIRTY READ} Select field1, field2, field3, case when field4=value1 then value1 else value2, (select field5 from othertable where othertable.field1=value) and so on (with several case-like fields)....
from table1
inner join table2 on field6=field7
inner join table3 on field8=field9
and several inner join
and several left join
where
(simple conditions...)

This select returns me an Error: Could not do a physical-order read to fetch next row

Anyway the "{SET ISOLATION TO DIRTY READ}" sentence before the select keyword always worked for me but in this select it doesn't and i don't know why.
I've tried to put "{SET ISOLATION TO DIRTY READ}" before the subselect too, at the joins..., but nothing works.

Any suggestions?

Thank you in advance.

Hi,

I may be wrong, but why do you state the isolation set statement between { } ?
Did you try taking the { } off ?

Set isolation is valid for the SESSION running, therefore the sub queries should understand it.

did you try running the same query from dbaccess?
Are you monitoring the locks, in order to check where your query is hitting against a lock?

Eric