Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SELECT NOT EXISTS

814680Nov 18 2010 — edited Nov 18 2010
HI

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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 16 2010
Added on Nov 18 2010
5 comments
460 views