max in exists where statement
523983Sep 25 2009 — edited Sep 25 2009I 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