CU_BAR_SR_00073_1 is a modification of the SR_00073 query to provide the Class Table with the columns needed for processing Student Course and Faculty Evaluations using the SmartEvals system.

This version should only be ran for testing purposes. When downloading for use in preparing the Student Course and Faculty Evaluation program and SmartEvals@Baruch Tool for creating data files for the SmartEvals system, variants CU_BAR_SR_00073_1A_NOWIN (No Winter Session Courses) or CU_BAR_SR_00073_1B_WIN (Winter Session Courses Only) should be ran.

 

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 (+) 
  ORDER BY 1, 2, 3, 5, 6, 7
  • No labels