Hi,

My Following select st. is running from last one day. I think the problem is NOT IN clause.
Please suggest the alternative select clause.



SELECT resreq.proj, resreq.ver, substr(resreq.act, -5, 5) act, resreq.rn, null,
null, resreq.rl, resreq.dr, resreq.ra
FROM resreq
WHERE resreq.ver IN ('0', '99')
AND NOT (resreq.proj, resreq.ver, substr(resreq.act, -5, 5), resreq.rn)
IN (SELECT actres.proj, actres.ver, substr(actres.act, -5, 5) act,
actres.rn
FROM actres, resreq
WHERE actres.proj = resreq.proj
AND actres.ver = resreq.ver
AND substr(actres.act, -5, 5) = substr(resreq.act, -5, 5)
AND actres.rn = resreq.rn
AND actres.ver IN ('0', '99'))




Best Regards,
Prashant Dalal
prashantdalal@hotmail.com
http://prashantdalal.port5.com

Try NOT EXISTS, with the subquery corellated (joined) to the main query.

DOES THIS WORK?

SELECT
resreq.proj,
resreq.ver,
substr(resreq.act, -5, 5) act,
resreq.rn,
null,
null,
resreq.rl,
resreq.dr,
resreq.ra
FROM
resreq
WHERE
resreq.ver IN ('0', '99')
AND (resreq.proj, resreq.ver, substr(resreq.act, -5, 5), resreq.rn)
NOT IN (SELECT
actres.proj,
actres.ver,
substr(actres.act, -5, 5) act,
actres.rn
FROM
actres,
resreq
WHERE
actres.proj = resreq.proj
AND actres.ver = resreq.ver
AND substr(actres.act, -5, 5) = substr(resreq.act, -5, 5)
AND actres.rn = resreq.rn
AND actres.ver IN ('0', '99'))