Background and Use
This is a list of currently Enrolled students and their enrollment. Data is used in support of the SmartEval service for student course evaluation process.
The query has prompt on Institution and Term, Acad Org.
Results Sample
Query SQL
SELECT CASE
WHEN SUBSTR ( A.STRM, 4, 1) = '2' THEN 'Sp-' || SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION
WHEN SUBSTR ( A.STRM, 4, 1) = '6' THEN 'Su-' || SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION
WHEN SUBSTR ( A.STRM, 4, 1) = '9' THEN 'Fa-' || SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION
end, C.EMPLID, A.SUBJECT, A.CATALOG_NBR, CASE
WHEN SUBSTR ( A.STRM, 4, 1) = '2' THEN 'Sp-' || SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION
WHEN SUBSTR ( A.STRM, 4, 1) = '6' THEN 'Su-' || SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION
WHEN SUBSTR ( A.STRM, 4, 1) = '9' THEN 'Fa-' || SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION
end, A.DESCR, A.SSR_COMPONENT, A.SESSION_CODE, A.CLASS_SECTION, A.ACAD_CAREER, 'Yes', B.EMPLID, B.FIRST_NAME, B.LAST_NAME, D.EMPLID, D.LAST_NAME, D.FIRST_NAME, E.EMAIL_ADDR, F.EMAIL_ADDR,A.CRSE_ID,A.CRSE_OFFER_NBR,A.STRM
FROM PS_CLASS_TBL A, PS_CLASS_INSTR_VW5 B, PS_STDNT_ENRL C, PS_CLASS_INSTR_VW5 D, PS_CU_BIO_VW E, PS_CU_BIO_VW F
WHERE A.INSTITUTION = :1
AND A.STRM = :2
AND A.ACAD_ORG = :3
AND A.CLASS_STAT = 'A'
AND A.ENRL_TOT > 0
AND A.STRM = C.STRM
AND A.SESSION_CODE = C.SESSION_CODE
AND C.ACAD_CAREER = A.ACAD_CAREER
AND C.INSTITUTION = A.INSTITUTION
AND C.CLASS_NBR = A.CLASS_NBR
AND A.CRSE_ID = B.CRSE_ID (+)
AND A.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR (+)
AND A.STRM = B.STRM (+)
AND A.SESSION_CODE = B.SESSION_CODE (+)
AND A.CLASS_SECTION = B.CLASS_SECTION (+)
AND 'PI' = B.INSTR_ROLE (+)
AND A.CRSE_ID = D.CRSE_ID (+)
AND A.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR (+)
AND A.STRM = D.STRM (+)
AND A.SESSION_CODE = D.SESSION_CODE (+)
AND A.CLASS_SECTION = D.CLASS_SECTION (+)
AND 'SI' = D.INSTR_ROLE (+)
AND C.CRSE_GRADE_OFF NOT IN ('*W','W','WU','WN','WA')
AND B.EMPLID = E.EMPLID (+)
AND D.EMPLID = F.EMPLID (+)
AND C.ENRL_STATUS_REASON = 'ENRL'
ORDER BY 3, 4, 9, 2