CU_BAR_SR_00073_2 is a version of the CU_BAR_SR_00073_1 query to provide the same information as the SR_00073_1 query but for one single section.

This version should be used when the SmartEvals@Baruch Tool indicates that a section is missing from the class table. This record will only return one section's information at a time.

 

Query Shared with: Denyse Ramkaran, Barry Ho, Arthur Downing, Patrick Ackerman, Tabassum Lodhi

 

 
Base
 
SELECT A.STRM, A.SESSION_CODE, A.INSTITUTION, A.ACAD_CAREER, A.SUBJECT, A.CATALOG_NBR, A.CLASS_SECTION, A.CLASS_NBR, A.DESCR, A.ENRL_TOT, A.ENRL_STAT, A.CLASS_STAT, A.CLASS_TYPE, A.ENRL_CAP, A.WAIT_CAP, A.WAIT_TOT, A.MIN_ENRL, A.SSR_COMPONENT, A.CRSE_ID, A.CRSE_OFFER_NBR, C.LAST_NAME, C.FIRST_NAME, TO_CHAR(A.START_DT,'YYYY-MM-DD'), TO_CHAR(A.END_DT,'YYYY-MM-DD'), '', '', '', '', CASE
WHEN INSTR(UPPER( A.DESCR),'IND STUD') > 0 THEN 'REMOVE'
WHEN INSTR(UPPER( A.DESCR),'THESIS') > 0 THEN 'REMOVE'
WHEN INSTR(UPPER( A.DESCR),'INTERNSHIP') > 0 THEN 'REMOVE'
WHEN INSTR(UPPER( A.DESCR),'FELLOWSHIP') > 0 THEN 'REMOVE'
WHEN INSTR(UPPER( A.DESCR),'GRAD INTERN MODULE') > 0 THEN 'REMOVE'
WHEN INSTR(UPPER( A.DESCR),'INTERNSHP') > 0 THEN 'REMOVE'
WHEN INSTR(UPPER( A.DESCR),'INTRNSHP') > 0 THEN 'REMOVE'
WHEN UPPER( A.SUBJECT) = 'MAM' THEN 'REMOVE'
WHEN UPPER( A.SUBJECT) = 'CUNBA' THEN 'REMOVE'
WHEN UPPER( A.SUBJECT) = 'STABD' THEN 'REMOVE'
WHEN UPPER( A.SUBJECT) = 'PERM' THEN 'REMOVE'
WHEN UPPER( A.SUBJECT) = 'FSPE' THEN 'REMOVE'
WHEN UPPER( A.SUBJECT) = 'FSPM' THEN 'REMOVE'
WHEN UPPER( A.SUBJECT) = 'IBSIP' THEN 'REMOVE'
ELSE ''
END, CASE
WHEN INSTR(UPPER( A.DESCR),'IND STUD') > 0 THEN 'IND STUDY'
WHEN INSTR(UPPER( A.DESCR),'THESIS') > 0 THEN 'THESIS'
WHEN INSTR(UPPER( A.DESCR),'INTERNSHIP') > 0 THEN 'INTERNSHIP'
WHEN INSTR(UPPER( A.DESCR),'FELLOWSHIP') > 0 THEN 'INTERNSHIP'
WHEN INSTR(UPPER( A.DESCR),'GRAD INTERN MODULE') > 0 THEN 'INTERNSHIP'
WHEN INSTR(UPPER( A.DESCR),'INTERNSHP') > 0 THEN 'INTERNSHIP'
WHEN INSTR(UPPER( A.DESCR),'INTRNSHP') > 0 THEN 'INTERNSHIP'
WHEN UPPER( A.SUBJECT) = 'MAM' THEN 'MAINT. MATRIC'
WHEN UPPER( A.SUBJECT) = 'CUNBA' THEN 'CUNYBA'
WHEN UPPER( A.SUBJECT) = 'STABD' THEN 'STUDY ABROAD'
WHEN UPPER( A.SUBJECT) = 'PERM' THEN 'EPERMIT'
WHEN UPPER( A.SUBJECT) = 'FSPE' THEN 'IMMERSION CLASS'
WHEN UPPER( A.SUBJECT) = 'FSPM' THEN 'IMMERSION CLASS'
WHEN UPPER( A.SUBJECT) = 'IBSIP' THEN 'IMMERSION CLASS'
ELSE ''
END, CASE
WHEN SUBSTR ( A.STRM, 4, 1) = '2' AND  A.SESSION_CODE = 'WIN' THEN 'Wn-' || SUBSTR ( A.STRM, 2, 2) || '-' ||  A.CRSE_ID || '-' ||  A.CLASS_SECTION 
WHEN SUBSTR ( A.STRM, 4, 1) = '2' AND NOT( A.SESSION_CODE = 'WIN') 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 
  FROM PS_CLASS_TBL A, PS_CLASS_INSTR B, PS_CU_BIO_VW C 
  WHERE A.INSTITUTION = :1 
     AND A.STRM = :2 
     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 B.EMPLID =  C.EMPLID (+) 
     AND ( A.CLASS_NBR = :4) 
  ORDER BY 1, 2, 3, 4, 5, 6, 7
  • No labels