The core search of this query is to find incomplete and discontinue students. Therefore, the query contains an Union and some sub queries.
The top level query is looking for students with multiple program at Baruch using through 2 ACAD_PROG records. It's also looking for A. PROG_STATUS = 'DC' (discontinue), E. PROG_STATUS != 'CM' ( not completed) students, using sub query using maximum term range to retrieve latest record.
In union1 the query is looking for discontinue (DC) students with one program at Baruch by using sub query ACAD_PROG does not exist.
Query SQL:
SELECT B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, A.ACAD_CAREER, A.STDNT_CAR_NBR, A.ACAD_PROG, K.ACAD_PLAN, M.SRVC_IND_CD, M.SRVC_IND_REASON, P.SRVC_IND_CD, P.SRVC_IND_REASON, A.ADMIT_TERM, A.EXP_GRAD_TERM, C.STRM, C.UNT_TAKEN_PRGRSS, C.UNT_PASSD_PRGRSS, C.TOT_TAKEN_PRGRSS, C.TOT_PASSD_PRGRSS, C.CUR_GPA, C.CUM_GPA, to_char ( E.STDNT_CAR_NBR, '99.'), E.ACAD_CAREER, E.ACAD_PROG, E.PROG_STATUS
FROM PS_ACAD_PROG A, PS_CU_BIO_VW B, PS_STDNT_CAR_TERM C, PS_ACAD_PROG E, PS_ACAD_PLAN K, PS_SRVC_IND_DATA M, PS_SRVC_IND_DATA P
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.ACAD_CAREER = A_ED.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.ACAD_CAREER = A_ES.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
AND A.EFFDT = A_ES.EFFDT)
AND A.INSTITUTION = :1
AND A.ACAD_CAREER = :2
AND A.EMPLID = B.EMPLID
AND A.PROG_STATUS = 'DC'
AND A.ACAD_PROG = :3
AND A.EMPLID = C.EMPLID
AND A.ACAD_CAREER = C.ACAD_CAREER
AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
AND C.INSTITUTION = A.INSTITUTION
AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
AND C.STRM = (SELECT max ( D.STRM)
FROM PS_STDNT_CAR_TERM D
WHERE ( D.EMPLID = C.EMPLID
AND D.ACAD_CAREER = C.ACAD_CAREER
AND D.INSTITUTION = C.INSTITUTION
AND D.STDNT_CAR_NBR = C.STDNT_CAR_NBR ))
AND A.EMPLID = E.EMPLID
AND A.ACAD_CAREER = E.ACAD_CAREER
AND A.STDNT_CAR_NBR <> E.STDNT_CAR_NBR
AND A.INSTITUTION = E.INSTITUTION
AND E.EFFDT =
(SELECT MAX(E_ED.EFFDT) FROM PS_ACAD_PROG E_ED
WHERE E.EMPLID = E_ED.EMPLID
AND E.ACAD_CAREER = E_ED.ACAD_CAREER
AND E.STDNT_CAR_NBR = E_ED.STDNT_CAR_NBR
AND E_ED.EFFDT <= SYSDATE)
AND E.EFFSEQ =
(SELECT MAX(E_ES.EFFSEQ) FROM PS_ACAD_PROG E_ES
WHERE E.EMPLID = E_ES.EMPLID
AND E.ACAD_CAREER = E_ES.ACAD_CAREER
AND E.STDNT_CAR_NBR = E_ES.STDNT_CAR_NBR
AND E.EFFDT = E_ES.EFFDT)
AND A.EMPLID = K.EMPLID
AND A.ACAD_CAREER = K.ACAD_CAREER
AND A.STDNT_CAR_NBR = K.STDNT_CAR_NBR
AND A.EFFSEQ = K.EFFSEQ
AND A.EFFDT = K.EFFDT
AND E.PROG_STATUS <> 'CM'
AND K.ACAD_PLAN NOT LIKE '%-MIN'
AND A.EMPLID = M.EMPLID (+)
AND A.INSTITUTION = M.INSTITUTION (+)
AND 'APS' = M.SRVC_IND_CD (+)
AND A.EMPLID = P.EMPLID (+)
AND A.INSTITUTION = P.INSTITUTION (+)
AND 'ACP' = P.SRVC_IND_CD (+)
AND substr ( B.LAST_NAME, 1, 5) <> 'ZZZZZ'
AND ( K.ACAD_PLAN = :4
OR :4 IS NULL) )
UNION
SELECT G.LAST_NAME, G.FIRST_NAME, G.MIDDLE_NAME, F.EMPLID, F.ACAD_CAREER, F.STDNT_CAR_NBR, F.ACAD_PROG, L.ACAD_PLAN, N.SRVC_IND_CD, N.SRVC_IND_REASON, O.SRVC_IND_CD, O.SRVC_IND_REASON, F.ADMIT_TERM, F.EXP_GRAD_TERM, H.STRM, H.UNT_TAKEN_PRGRSS, H.UNT_PASSD_PRGRSS, H.TOT_TAKEN_PRGRSS, H.TOT_PASSD_PRGRSS, H.CUR_GPA, H.CUM_GPA, ' ', ' ', ' ', ' '
FROM PS_ACAD_PROG F, PS_CU_BIO_VW G, PS_STDNT_CAR_TERM H, PS_ACAD_PLAN L, PS_SRVC_IND_DATA N, PS_SRVC_IND_DATA O
WHERE ( F.EFFDT =
(SELECT MAX(F_ED.EFFDT) FROM PS_ACAD_PROG F_ED
WHERE F.EMPLID = F_ED.EMPLID
AND F.ACAD_CAREER = F_ED.ACAD_CAREER
AND F.STDNT_CAR_NBR = F_ED.STDNT_CAR_NBR
AND F_ED.EFFDT <= SYSDATE)
AND F.EFFSEQ =
(SELECT MAX(F_ES.EFFSEQ) FROM PS_ACAD_PROG F_ES
WHERE F.EMPLID = F_ES.EMPLID
AND F.ACAD_CAREER = F_ES.ACAD_CAREER
AND F.STDNT_CAR_NBR = F_ES.STDNT_CAR_NBR
AND F.EFFDT = F_ES.EFFDT)
AND F.EMPLID = G.EMPLID
AND F.INSTITUTION = :1
AND F.ACAD_CAREER = :2
AND F.ACAD_PROG = :3
AND F.PROG_STATUS = 'DC'
AND F.EMPLID = H.EMPLID
AND F.ACAD_CAREER = H.ACAD_CAREER
AND F.STDNT_CAR_NBR = H.STDNT_CAR_NBR
AND F.INSTITUTION = H.INSTITUTION
AND H.STRM = (SELECT max( I.STRM)
FROM PS_STDNT_CAR_TERM I
WHERE ( I.EMPLID = H.EMPLID
AND I.ACAD_CAREER = H.ACAD_CAREER
AND I.INSTITUTION = H.INSTITUTION
AND I.STDNT_CAR_NBR = H.STDNT_CAR_NBR ))
AND NOT EXISTS (SELECT J.EMPLID
FROM PS_ACAD_PROG J
WHERE ( J.EFFDT =
(SELECT MAX(J_ED.EFFDT) FROM PS_ACAD_PROG J_ED
WHERE J.EMPLID = J_ED.EMPLID
AND J.ACAD_CAREER = J_ED.ACAD_CAREER
AND J.STDNT_CAR_NBR = J_ED.STDNT_CAR_NBR
AND J_ED.EFFDT <= SYSDATE)
AND J.EFFSEQ =
(SELECT MAX(J_ES.EFFSEQ) FROM PS_ACAD_PROG J_ES
WHERE J.EMPLID = J_ES.EMPLID
AND J.ACAD_CAREER = J_ES.ACAD_CAREER
AND J.STDNT_CAR_NBR = J_ES.STDNT_CAR_NBR
AND J.EFFDT = J_ES.EFFDT)
AND J.EMPLID = F.EMPLID
AND J.ACAD_CAREER = F.ACAD_CAREER
AND J.STDNT_CAR_NBR <> F.STDNT_CAR_NBR
AND J.INSTITUTION = F.INSTITUTION ))
AND F.EMPLID = L.EMPLID
AND F.ACAD_CAREER = L.ACAD_CAREER
AND F.STDNT_CAR_NBR = L.STDNT_CAR_NBR
AND F.EFFSEQ = L.EFFSEQ
AND F.EFFDT = L.EFFDT
AND L.ACAD_PLAN NOT LIKE '%-MIN'
AND F.EMPLID = N.EMPLID (+)
AND F.INSTITUTION = N.INSTITUTION (+)
AND 'APS' = N.SRVC_IND_CD (+)
AND F.EMPLID = O.EMPLID (+)
AND F.INSTITUTION = O.INSTITUTION (+)
AND 'ACP' = O.SRVC_IND_CD (+)
AND substr ( G.LAST_NAME, 1, 5) <> 'ZZZZZ'
AND ( L.ACAD_PLAN = :4
OR :4 IS NULL) )
ORDER BY 1, 2, 3