SELECT NOT EXISTS
814680Nov 18 2010 — edited Nov 18 2010HI
A newbie question
This is my SQL below
SELECT a.*
FROM CASH_FLOW_EVENT_DETAIL a
WHERE TO_DATE(a.EVENT_DATE) = '25-Jan-2011'
AND a.buy_sell = 'B'
AND NOT EXISTS (
SELECT DISTINCT DEAL_ID
FROM INVOICE_CASHFLOWS c, INVOICE_HEADER_INFORMATION e
WHERE c.DOC_NUM = e.DOC_NUM
AND CONCAT(e.PREVIOUS_DOC_STATUS,e.CURRENT_DOC_STATUS) != 'GeneratedCancelled'
AND (e.doc_num, e.version_num) IN
(SELECT f.DOC_NUM, MAX(f.VERSION_NUM)
FROM INVOICE_HEADER_INFORMATION f
GROUP BY f.DOC_NUM)
)
Now, the following SQL returns a row
SELECT a.*
FROM CASH_FLOW_EVENT_DETAIL a
WHERE TO_DATE(a.EVENT_DATE) = '25-Jan-2011'
AND a.buy_sell = 'B'
DEAL_ID is a column in this resultset.
Howeevr, when I attach the NOT EXISTS clause, that row goes away. It shouldnt because The SELECT under NOT EXISTS does not contain that DEAL_ID.