Formerly CU_BAR_SR_00035_1
Background and Use
The data is used assignment of student Active Directory ID/SAM and Baruchmail email address. Query is for students with term activation for specified term. Query included DOB, National ID (NID), if exists, or external system ID type "LID" (assigned SSN).
Since it retrieves only one term data and Fall term contain both summer and fall term data. It was inconvenient to generate data 2 different time within the same semester. Therefore, Patrick proposed to include 'term range' in the prompt in this query.
Final Report Delivery with requirements:
The query for ADID Updates Active Term Range CU_BAR_SR_00035_1 has been shared with Patrick and Dixon and the query is working fine.
CU_BAR_SR_00030_1 Prompts are below:
- Institution: Bar01
- From Enroll Term: pick any term
- To Enroll Term: pick any term
Query Development:
Following table shows all necessary records that have been used to generate fields as requested by an user to create this report.
Records | Description of fields |
---|---|
STDNT_CAR_TERM | EMPLID, STERM, ACAD_CAREER |
NAME_CURR_VW | LAST_NAME, FIRST_NAME |
SCC_PERS_NI_QVW | NATIONAL_ID_TYPE, NATIONAL_ID |
PERSNL_FERPA_VW | BIRTHDATE |
Subquery for A.STRM - Term | |
STDNT_CAR_TERM | max (G.STRM) |
UNION | |
STDNT_CAR_TERM | EMPLID, ACAD_CAREER, STRM |
NAME_CURR_VW | LAST_NAME, FIRST_NAME |
EXTERNAL_SYSTEM | EXTERNAL_SYSTEM, EXTERNAL_SYSTEM_ID |
PERSNL_FERPA_VW | BIRTHDATE |
Subquery for exists | |
SCC_PERS_NI_QVW | EMPLID |
Subquery for E.STRM - Term | |
STDNT_CAR_TERM | max (D.STRM) |
Legend of Query:
Expressions:
Few 'Expressions' have been added to find 'Maximum term' which pick up the latest update about students such as 'max (G.STRM)','max (D.STRM)' . 'SUBSTR(C.NATIONAL_ID,6,4)', 'SUBSTR(I.NATIONAL_ID,1,1)' have been used to mask national id.
Prompt:
Institution, From Enroll Term and To Enroll Term have been mandatory for an user to input.
Criteria:
Query Output Image:
Query SQL:
SELECT A.EMPLID, A.ACAD_CAREER, A.STRM, B.LAST_NAME, B.FIRST_NAME, C.NATIONAL_ID_TYPE, C.NATIONAL_ID, TO_CHAR(J.BIRTHDATE,'YYYY-MM-DD')
FROM PS_STDNT_CAR_TERM A, PS_NAME_CURR_VW B, PS_SCC_PERS_NI_QVW C, PS_PERSNL_FERPA_VW J
WHERE ( A.INSTITUTION = :1
AND A.STRM BETWEEN :2 AND :3
AND A.EMPLID = B.EMPLID
AND B.NAME_TYPE = 'PRI'
AND A.EMPLID = C.EMPLID (+)
AND A.STRM = (SELECT max ( G.STRM)
FROM PS_STDNT_CAR_TERM G
WHERE ( G.EMPLID = A.EMPLID
AND G.ACAD_CAREER = A.ACAD_CAREER
AND G.INSTITUTION = A.INSTITUTION
AND G.STDNT_CAR_NBR = A.STDNT_CAR_NBR ))
AND A.EMPLID = J.EMPLID(+) )
UNION
SELECT E.EMPLID, E.ACAD_CAREER, E.STRM, F.LAST_NAME, F.FIRST_NAME, H.EXTERNAL_SYSTEM, H.EXTERNAL_SYSTEM_ID, TO_CHAR(K.BIRTHDATE,'YYYY-MM-DD')
FROM PS_STDNT_CAR_TERM E, PS_NAME_CURR_VW F, PS_EXTERNAL_SYSTEM H, PS_PERSNL_FERPA_VW K
WHERE ( E.EMPLID = F.EMPLID
AND E.EMPLID = H.EMPLID
AND H.EFFDT =
(SELECT MAX(H_ED.EFFDT) FROM PS_EXTERNAL_SYSTEM H_ED
WHERE H.EMPLID = H_ED.EMPLID
AND H.EXTERNAL_SYSTEM = H_ED.EXTERNAL_SYSTEM
AND H_ED.EFFDT <= SYSDATE)
AND H.EXTERNAL_SYSTEM = 'LID'
AND E.INSTITUTION = :1
AND E.STRM BETWEEN :2 AND :3
AND EXISTS (SELECT I.EMPLID
FROM PS_SCC_PERS_NI_QVW I
WHERE ( I.EMPLID = E.EMPLID
AND SUBSTR( I.NATIONAL_ID,1,1) = 'X' ))
AND E.STRM = (SELECT max ( D.STRM)
FROM PS_STDNT_CAR_TERM D
WHERE ( D.EMPLID = E.EMPLID
AND D.ACAD_CAREER = E.ACAD_CAREER
AND D.INSTITUTION = E.INSTITUTION
AND D.STDNT_CAR_NBR = E.STDNT_CAR_NBR ))
AND E.EMPLID = K.EMPLID(+)
AND F.NAME_TYPE = 'PRI' )