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 career at Baruch
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