CU_BAR_CM_00003_2

Query SQL:

SELECT A.ACAD_GROUP, A.ACAD_ORG, A.SUBJECT, A.CATALOG_NBR, A.DESCR, A.SESSION_CODE, A.CLASS_NBR, A.CLASS_SECTION, A.CLASS_STAT, A.INSTRUCTION_MODE, TO_CHAR(A.START_DT,'YYYY-MM-DD'), TO_CHAR(A.END_DT,'YYYY-MM-DD'), A.ROOM_CAP_REQUEST, A.ENRL_CAP, A.ENRL_TOT, A.ENRL_CAP - A.ENRL_TOT, D.DESCRFORMAL, F.RQRMNT_GROUP, A.ACAD_CAREER, C.UNITS_ACAD_PROG, C.CRSE_CONTACT_HRS, case

when A.ACAD_CAREER = 'UGRD'

then ( A.ENRL_TOT * C.UNITS_ACAD_PROG) / 15

else ( A.ENRL_TOT * C.UNITS_ACAD_PROG) / 12

end, E.CLASS_MTG_NBR, E.FACILITY_ID, TO_CHAR(CAST((E.MEETING_TIME_START) AS TIMESTAMP),'HH24.MI.SS.FF'), TO_CHAR(CAST((E.MEETING_TIME_END) AS TIMESTAMP),'HH24.MI.SS.FF'), decode ( E.MON, 'Y', 'Mo') || decode ( E.TUES, 'Y', 'Tu') || decode ( E.WED, 'Y', 'We') || decode ( E.THURS, 'Y', 'Th') || decode ( E.FRI, 'Y', 'Fr') || decode ( E.SAT, 'Y', 'Sa') || decode ( E.SUN, 'Y', 'Su'), case

when substr ( B.LAST_NAME, 1, 1) <> ' '

then B.LAST_NAME || ', ' || B.FIRST_NAME

else ' '

end, TO_CHAR(SYSDATE,'YYYY-MM-DD'),A.CRSE_ID,A.CRSE_OFFER_NBR,A.STRM,D.RQMNT_DESIGNTN,TO_CHAR(D.EFFDT,'YYYY-MM-DD')

FROM PS_CLASS_TBL A, PS_CLASS_ASSOC C, PS_RQMNT_DESIG_TBL D, PS_CLASS_MTG_PAT E, PS_CLASS_INSTR_VW5 B, PS_CRSE_OFFER F

WHERE ( A.INSTITUTION = :1

AND A.STRM = :2

AND A.CRSE_ID = C.CRSE_ID

AND A.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR

AND A.STRM = C.STRM

AND A.SESSION_CODE = C.SESSION_CODE

AND A.ASSOCIATED_CLASS = C.ASSOCIATED_CLASS

AND D.RQMNT_DESIGNTN = C.RQMNT_DESIGNTN

AND D.EFFDT =

(SELECT MAX(D_ED.EFFDT) FROM PS_RQMNT_DESIG_TBL D_ED

WHERE D.RQMNT_DESIGNTN = D_ED.RQMNT_DESIGNTN

~~AND D_ED.EFFDT <= SYSDATE~~)

AND A.CRSE_ID = E.CRSE_ID (+)

AND A.CRSE_OFFER_NBR = E.CRSE_OFFER_NBR (+)

AND A.STRM = E.STRM (+)

AND A.SESSION_CODE = E.SESSION_CODE (+)

AND A.CLASS_SECTION = E.CLASS_SECTION (+)

AND E.CRSE_ID = B.CRSE_ID (+)

AND E.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR (+)

AND E.STRM = B.STRM (+)

AND E.SESSION_CODE = B.SESSION_CODE (+)

AND E.CLASS_SECTION = B.CLASS_SECTION (+)

AND E.CLASS_MTG_NBR = B.CLASS_MTG_NBR (+)

AND C.CRSE_ID = F.CRSE_ID

AND C.CRSE_OFFER_NBR = F.CRSE_OFFER_NBR

AND F.EFFDT =

(SELECT MAX(F_ED.EFFDT) FROM PS_CRSE_OFFER F_ED

WHERE F.CRSE_ID = F_ED.CRSE_ID

~~AND F_ED.EFFDT <= SYSDATE)~~

AND ( A.ACAD_GROUP = :3

OR :3 IS NULL)

AND ( A.ACAD_ORG = :4

OR :4 IS NULL)

AND ( A.SESSION_CODE = :5

OR :5 IS NULL)

AND ( A.ACAD_CAREER = :6

OR :6 IS NULL) )

ORDER BY 1, 2, 3, 4, 8, 23

CU_BAR_CM_00003_3

The Query has been modified from the original because the effective date was missing some of the classes as 6/20/2017: see below the request:

I use a query that has the enrollment information for all of the classes in a specific semester. For Fall 2017 BUS 1011 and 1011H, and POL 5030 are new classes that do not show up in my query (CU_BAR_CM_00003_2). I don’t know if there are other new courses that are not captured.

Can this be fixed?

Thanks. Stephanie

Fixed issue:effective date

D.EFFDT - Effective Date

Last Eff Date

F.EFFDT - Effective Date