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!

max in exists where statement

523983Sep 25 2009 — edited Sep 25 2009
I would like to be able to use max(date) in an exists subquery WHERE statement
if the first condition is not met but would be met with the second condition
the sql query is below

Thank you

SELECT DISTINCT
gc.CK1, gc.FFY, q4.CMI,
q4.INDV, q4.RSH, gc.DCC,
gc.CN_ID, gc.SID, q4.CRDT
FROM grad_Course gc,
(SELECT q2.DCC, q2.CN_ID, q2.SID,
q2.CMI, q2.INDV, q2.RSH,
q2.CRDT, q2.CHG_DT
FROM CMHT q2
where (( q2.BEG_DT <= '30-SEP-2008'
and NVL(q2.END_DT, '30-SEP-2008') >= '01-SEP-2007')
or q2.RSH = '01')
and q2.DCC = '01'
and EXISTS (SELECT 'x'
FROM TCMA s6
WHERE (s6.DCC = q2.DCC
AND s6.CMI = q2.CMI
AND s6.ACTCD in('m1','n3','tr','mw')
AND s6.PCX in('his','hers','dog')
AND s6.DCC = '01'
AND s6.BEG_DT <= '30-SEP-2008')
AND ( (NVL(s6.END_DT, '30-SEP-2008') >= '01-SEP-2007')
OR *(MAX(END_DT) <= '01-SEP-2007'*

AND CHG_DT > '30-SEP-2008' ))
)
) q4
WHERE gc.DCC = q4.DCC (+)
AND gc.CN_ID = q4.CN_ID (+)
AND gc.SID = q4.SID (+)
and gc.DCC = '01'
ORDER BY q4.CMI desc, gc.CK1, CM
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 23 2009
Added on Sep 25 2009
3 comments
1,057 views