I was wondering if anyone could give me an alternate (and more efficient) way to perform this query:

select tdsls995.*
from tdsls995 for update
where tdsls995.orno not in
(
select ifc.orno
from tdsls999 ifc, tdsls045 d
where ifc.seqn = 0
and ifc.sorn = d.orno
)

I'm basically looking for the equivalent of the ANSI SQL syntax:

select tdsls995.*
from tdsls995 ah left outer join tdsls999 ifc on ah.orno = ifc.orno
left outer join tdsls045 d on ifc.sorn = d.orno
where ((ifc.seqn = 0)
or (ifc.orno is null))
and d.orno is null

The first query works, but it take a REALLY long time to run. Any help is appreciated.

Thanks,

Vince

Hi,

try the following code:


select tdsls995.*
from tdsls995 for update
where not exists( select tdsls999.orno
from tdsls999, tdsls045
where tdsls045._index1 = {tdsls995.orno}
and tdsls999.orno = tdsls045.orno
and tdsls999.seqn = 0)