Legend of this query:
Image of the query:
SELECT CASE WHEN E.TEST_COMPONENT = 'FINAL' AND F.CU_ESL_INDICATOR <> 'Y' AND E.SCORE > 55 THEN 'E210' WHEN E.TEST_COMPONENT = 'WEF' AND F.CU_ESL_INDICATOR <> 'Y' AND E.SCORE > 6 THEN 'E210' WHEN E.TEST_COMPONENT = 'WRF' AND F.CU_ESL_INDICATOR <> 'Y' AND E.SCORE > 7 THEN 'E210' WHEN E.TEST_COMPONENT = 'FINAL' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE > 71 THEN 'E210' WHEN E.TEST_COMPONENT = 'WEF' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE > 8 THEN 'E210' WHEN E.TEST_COMPONENT = 'WRF' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE > 8 THEN 'E210' WHEN E.TEST_COMPONENT = 'FINAL' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE BETWEEN 56 AND 71 THEN 'E21T' WHEN E.TEST_COMPONENT = 'WEF' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE BETWEEN 7 AND 8 THEN 'E21T' WHEN E.TEST_COMPONENT = 'WRF' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE = 8 THEN 'E21T' WHEN E.TEST_COMPONENT = 'FINAL' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE BETWEEN 44 AND 55 THEN 'E132' WHEN E.TEST_COMPONENT = 'WEF' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE = 6 THEN 'E132' WHEN E.TEST_COMPONENT = 'WRF' AND F.CU_ESL_INDICATOR = 'Y' AND E.SCORE BETWEEN 6 AND 7 THEN 'E132' ELSE 'NOEN' END, A.EMPLID, C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, K.NATIONAL_ID, A.ADMIT_TYPE, C.PHONE, C.EMAIL_ADDR, E.TEST_ID, E.TEST_COMPONENT, E.SCORE, TO_CHAR(E.TEST_DT,'YYYY-MM-DD'), F.CU_ESL_INDICATOR FROM PS_ADM_MC_VW A, PS_CU_BIO_VW C, PS_STDNT_TEST_COMP E, PS_SCC_PERS_NI_QVW K, PS_CU_ST_TEST_COMP F 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 = :1 AND A.ACAD_CAREER = :2 AND A.ADMIT_TERM BETWEEN :3 AND :4 AND ( A.ADMIT_TYPE = :5 OR :5 IS NULL) AND A.ACAD_PROG = 'UGRD' AND A.EMPLID = C.EMPLID AND A.EMPLID = K.EMPLID AND NOT EXISTS (SELECT B.EMPLID FROM PS_STDNT_GRPS_HIST B WHERE 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.EMPLID = A.EMPLID AND B.INSTITUTION = A.INSTITUTION AND B.STDNT_GROUP IN ('E210','E21T','E132','NOEN') AND B.EFF_STATUS = 'A') AND NOT EXISTS (SELECT D.EMPLID FROM PS_STDNT_TEST_COMP D WHERE D.EMPLID = A.EMPLID AND D.TEST_ID = 'WRIT EXEMPT') AND E.EMPLID = A.EMPLID AND E.EMPLID = F.EMPLID (+) AND E.TEST_ID = F.TEST_ID (+) AND E.TEST_COMPONENT = F.TEST_COMPONENT (+) AND E.TEST_DT = F.TEST_DT (+) AND E.LS_DATA_SOURCE = F.LS_DATA_SOURCE (+) AND F.TEST_DT = (SELECT MAX ( T.TEST_DT) FROM PS_STDNT_TEST_COMP T WHERE T.EMPLID = E.EMPLID AND T.TEST_COMPONENT IN ('FINAL','WEF','WRF')) AND (( E.TEST_ID = 'CNY-WRITING' AND E.TEST_COMPONENT = 'WRF') OR ( E.TEST_ID = 'CATW' AND E.TEST_COMPONENT = 'FINAL') OR ( E.TEST_ID = 'CUNY-ESSAY' AND E.TEST_COMPONENT = 'WEF')) UNION SELECT 'E210', M.EMPLID, N.LAST_NAME, N.FIRST_NAME, N.MIDDLE_NAME, O.NATIONAL_ID, M.ADMIT_TYPE, N.PHONE, N.EMAIL_ADDR, P.TEST_ID, ' ', 0, ' ', ' ' FROM PS_ADM_MC_VW M, PS_CU_BIO_VW N, PS_SCC_PERS_NI_QVW O, PS_STDNT_TEST_COMP P 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.INSTITUTION = :1 AND M.ACAD_CAREER = :2 AND M.ADMIT_TERM BETWEEN :3 AND :4 AND M.ADMIT_TYPE = :5 AND M.EMPLID = N.EMPLID AND M.EMPLID = O.EMPLID AND M.EMPLID = P.EMPLID AND P.TEST_ID = 'WRIT EXEMPT' AND NOT EXISTS (SELECT Q.EMPLID FROM PS_STDNT_GRPS_HIST Q WHERE Q.EFFDT = (SELECT MAX(Q_ED.EFFDT) FROM PS_STDNT_GRPS_HIST Q_ED WHERE Q.EMPLID = Q_ED.EMPLID AND Q.INSTITUTION = Q_ED.INSTITUTION AND Q.STDNT_GROUP = Q_ED.STDNT_GROUP AND Q_ED.EFFDT <= SYSDATE) AND Q.EMPLID = M.EMPLID AND Q.INSTITUTION = Q.INSTITUTION AND Q.EFF_STATUS = 'A' AND Q.STDNT_GROUP IN ('E210','E21T','E132','NOEN')) ORDER BY 3, 4, 5 |