Subquery rewrite from Oracle

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Help! Long story short - I need to convert this query from SQL used by
Oracle to SQL used by Access. I get stuck down in the WHERE Subquery.
I've spun my wheels today searching on-line and reading from all the books
I own... I think I need to use EXISTS, but I can't figure out how.

Let me know if any more information would helpful!

Thanks!

Karrie


SELECT
MSR_SR_NUM, MSR_SITE_ID, MSRA_MODEL_NUM, MSRA_SERIAL_NUM, MSRA_BILL_CODE,
MSR_OPEN_DATE_TIME, MSR_USR_ID
FROM
VRSC.MCS_SVC_REQ, VRCS.MCS_SVC_REQ_LINE, VRSC.MCS_SVC_REQ_ACTION
WHERE
MSR_SR_NUM = MSRL_SR_NUM AND
MSRL_SR_NUM = MSRA_SR_NUM and
MSRA_ALARM_NOTIFY_NBR is null AND
(MSRA_SERIAL_NUM, MSRA_MODEL_NUM, MSR_SITE_ID) NOT IN (SELECT
MSER_SERIAL_NUM, MSER_MODEL_NUM, MSER_SITE_ID FROM VRSC.MCS_SERIAL WHERE
MSER_STATUS=4 AND MSER_SITE_ID IS NOT NULL)AND
TRUNC(MSR_LAST_EDIT) = TRUNC(SYSDATE-1)
 
Hi,



you have to change the Oracle code:

....
FROM x
WHERE (x.a, x.b) IN (SELECT c, d FROM y )


into


....
FROM x
WHERE EXISTS( SELECT * FROM y WHERE x.a=y.c AND x.b=y.d)




and Oracle code:

....
FROM x
WHERE (x.a, x.b) NOT IN (SELECT c, d FROM y)

into

....
FROM x LEFT JOIN y
ON x.a=y.c AND x.b=y.d
WHERE y.c IS NULL






Hoping it may help,
Vanderghast, Access MVP
 
Back
Top