Legend of the Query:

Results

 

 
SELECT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPLID, A.ADMIT_TYPE, C.PHONE, C.EMAIL_ADDR, 'Missing M1'
FROM PS_ADM_MC_VW A, PS_STDNT_GRPS_HIST B, PS_CU_BIO_VW C
WHERE A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_ADM_MC_VW 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.ADM_APPL_NBR = A_ED.ADM_APPL_NBR
AND A.APPL_PROG_NBR = A_ED.APPL_PROG_NBR
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_ADM_MC_VW 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.ADM_APPL_NBR = A_ES.ADM_APPL_NBR
AND A.APPL_PROG_NBR = A_ES.APPL_PROG_NBR
AND A.EFFDT = A_ES.EFFDT)
AND A.INSTITUTION = 'BAR01'
AND A.ACAD_CAREER = 'UGRD'
AND A.EMPLID = B.EMPLID
AND B.INSTITUTION = A.INSTITUTION
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_GRPS_HIST B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.INSTITUTION = B_ED.INSTITUTION
AND B.STDNT_GROUP = B_ED.STDNT_GROUP
AND B_ED.EFFDT <= SYSDATE)
AND B.STDNT_GROUP = 'SEEK'
AND B.EFF_STATUS = 'A'
AND A.ADMIT_TERM BETWEEN :1 AND :2
AND A.EMPLID = C.EMPLID
AND NOT EXISTS (SELECT D.EMPLID
FROM PS_STDNT_TEST_COMP D
WHERE D.EMPLID = A.EMPLID
AND D.TEST_ID = 'ACT-MATH-1'
AND D.TEST_COMPONENT = 'M1')
UNION
SELECT F.LAST_NAME, F.FIRST_NAME, F.MIDDLE_NAME, E.EMPLID, E.ADMIT_TYPE, F.PHONE, F.EMAIL_ADDR, 'Missing M2'
FROM PS_ADM_MC_VW E, PS_CU_BIO_VW F, PS_STDNT_GRPS_HIST G
WHERE E.EFFDT =
(SELECT MAX(E_ED.EFFDT) FROM PS_ADM_MC_VW 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.ADM_APPL_NBR = E_ED.ADM_APPL_NBR
AND E.APPL_PROG_NBR = E_ED.APPL_PROG_NBR
AND E_ED.EFFDT <= SYSDATE)
AND E.EFFSEQ =
(SELECT MAX(E_ES.EFFSEQ) FROM PS_ADM_MC_VW 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.ADM_APPL_NBR = E_ES.ADM_APPL_NBR
AND E.APPL_PROG_NBR = E_ES.APPL_PROG_NBR
AND E.EFFDT = E_ES.EFFDT)
AND E.INSTITUTION = 'BAR01'
AND E.ACAD_CAREER = 'UGRD'
AND E.ADMIT_TERM BETWEEN :1 AND :2
AND E.EMPLID = F.EMPLID
AND E.EMPLID = G.EMPLID
AND G.INSTITUTION = E.INSTITUTION
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_STDNT_GRPS_HIST G_ED
WHERE G.EMPLID = G_ED.EMPLID
AND G.INSTITUTION = G_ED.INSTITUTION
AND G.STDNT_GROUP = G_ED.STDNT_GROUP
AND G_ED.EFFDT <= SYSDATE)
AND G.STDNT_GROUP = 'SEEK'
AND G.EFF_STATUS = 'A'
AND NOT EXISTS (SELECT H.EMPLID
FROM PS_STDNT_TEST_COMP H
WHERE H.EMPLID = E.EMPLID
AND H.TEST_ID = 'ACT-MATH-2'
AND H.TEST_COMPONENT = 'M2')
UNION
SELECT J.LAST_NAME, J.FIRST_NAME, J.MIDDLE_NAME, I.EMPLID, I.ADMIT_TYPE, J.PHONE, J.EMAIL_ADDR, 'Missing CATW'
FROM PS_ADM_MC_VW I, PS_CU_BIO_VW J, PS_STDNT_GRPS_HIST K
WHERE I.EFFDT =
(SELECT MAX(I_ED.EFFDT) FROM PS_ADM_MC_VW I_ED
WHERE I.EMPLID = I_ED.EMPLID
AND I.ACAD_CAREER = I_ED.ACAD_CAREER
AND I.STDNT_CAR_NBR = I_ED.STDNT_CAR_NBR
AND I.ADM_APPL_NBR = I_ED.ADM_APPL_NBR
AND I.APPL_PROG_NBR = I_ED.APPL_PROG_NBR
AND I_ED.EFFDT <= SYSDATE)
AND I.EFFSEQ =
(SELECT MAX(I_ES.EFFSEQ) FROM PS_ADM_MC_VW I_ES
WHERE I.EMPLID = I_ES.EMPLID
AND I.ACAD_CAREER = I_ES.ACAD_CAREER
AND I.STDNT_CAR_NBR = I_ES.STDNT_CAR_NBR
AND I.ADM_APPL_NBR = I_ES.ADM_APPL_NBR
AND I.APPL_PROG_NBR = I_ES.APPL_PROG_NBR
AND I.EFFDT = I_ES.EFFDT)
AND I.EMPLID = J.EMPLID
AND I.EMPLID = K.EMPLID
AND K.INSTITUTION = I.INSTITUTION
AND K.EFFDT =
(SELECT MAX(K_ED.EFFDT) FROM PS_STDNT_GRPS_HIST K_ED
WHERE K.EMPLID = K_ED.EMPLID
AND K.INSTITUTION = K_ED.INSTITUTION
AND K.STDNT_GROUP = K_ED.STDNT_GROUP
AND K_ED.EFFDT <= SYSDATE)
AND K.EFF_STATUS = 'A'
AND K.STDNT_GROUP = 'SEEK'
AND I.INSTITUTION = 'BAR01'
AND I.ACAD_CAREER = 'UGRD'
AND NOT EXISTS (SELECT L.EMPLID
FROM PS_STDNT_TEST_COMP L
WHERE L.EMPLID = I.EMPLID
AND L.TEST_ID = 'CATW')
AND I.ADMIT_TERM BETWEEN :1 AND :2
UNION
SELECT N.LAST_NAME, N.FIRST_NAME, N.MIDDLE_NAME, M.EMPLID, M.ADMIT_TYPE, N.PHONE, N.EMAIL_ADDR, 'Missing CNY-READING'
FROM PS_ADM_MC_VW M, PS_CU_BIO_VW N, PS_STDNT_GRPS_HIST O
WHERE M.EFFDT =
(SELECT MAX(M_ED.EFFDT) FROM PS_ADM_MC_VW M_ED
WHERE M.EMPLID = M_ED.EMPLID
AND M.ACAD_CAREER = M_ED.ACAD_CAREER
AND M.STDNT_CAR_NBR = M_ED.STDNT_CAR_NBR
AND M.ADM_APPL_NBR = M_ED.ADM_APPL_NBR
AND M.APPL_PROG_NBR = M_ED.APPL_PROG_NBR
AND M_ED.EFFDT <= SYSDATE)
AND M.EFFSEQ =
(SELECT MAX(M_ES.EFFSEQ) FROM PS_ADM_MC_VW M_ES
WHERE M.EMPLID = M_ES.EMPLID
AND M.ACAD_CAREER = M_ES.ACAD_CAREER
AND M.STDNT_CAR_NBR = M_ES.STDNT_CAR_NBR
AND M.ADM_APPL_NBR = M_ES.ADM_APPL_NBR
AND M.APPL_PROG_NBR = M_ES.APPL_PROG_NBR
AND M.EFFDT = M_ES.EFFDT)
AND M.EMPLID = N.EMPLID
AND M.EMPLID = O.EMPLID
AND O.INSTITUTION = M.INSTITUTION
AND O.EFFDT =
(SELECT MAX(O_ED.EFFDT) FROM PS_STDNT_GRPS_HIST O_ED
WHERE O.EMPLID = O_ED.EMPLID
AND O.INSTITUTION = O_ED.INSTITUTION
AND O.STDNT_GROUP = O_ED.STDNT_GROUP
AND O_ED.EFFDT <= SYSDATE)
AND O.STDNT_GROUP = 'SEEK'
AND O.EFF_STATUS = 'A'
AND M.INSTITUTION = 'BAR01'
AND M.ACAD_CAREER = 'UGRD'
AND M.ADMIT_TERM BETWEEN :1 AND :2
AND NOT EXISTS (SELECT P.EMPLID
FROM PS_STDNT_TEST_COMP P
WHERE P.EMPLID = M.EMPLID
AND P.TEST_ID = 'CNY-READING')
ORDER BY 1, 2, 3
  • No labels