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_TERMEMPLID, STERM, ACAD_CAREER
NAME_CURR_VWLAST_NAME, FIRST_NAME
SCC_PERS_NI_QVWNATIONAL_ID_TYPE, NATIONAL_ID
PERSNL_FERPA_VWBIRTHDATE
Subquery for A.STRM - Term
 
STDNT_CAR_TERMmax (G.STRM)
UNION 
STDNT_CAR_TERMEMPLID, ACAD_CAREER, STRM
NAME_CURR_VWLAST_NAME, FIRST_NAME
EXTERNAL_SYSTEMEXTERNAL_SYSTEM, EXTERNAL_SYSTEM_ID
PERSNL_FERPA_VWBIRTHDATE
Subquery for exists
 
SCC_PERS_NI_QVW EMPLID
Subquery for E.STRM - Term
 
STDNT_CAR_TERMmax (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:


Top Level of Query:
STERM is looking for maximum term through sub query and NAME_TYPE is primery 'PRI'. 

UNION:
In the union it's looking for 'LID'  through  EXTERNAL_SYSTEM.  However, NAME_TYPE is still looking for primary 'PRI' to avoid duplication.  In the sub query it's looking for 'x' value in national_id if any exist.  STERM is looking for maximum term through sub query as well.

 

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' )

  • No labels