Background and Use:
This data set is used to check and update email address for (type) CAMPUS. The query is students with greater than 0 credits in specified term who are not in one of the "Permit In" student groups (begin "PI" or "CP").
Results Sample
Query SQL:
SELECT DISTINCT A.EMPLID, C.EMAIL_ADDR
FROM PS_STDNT_CAR_TERM A, PS_EMAIL_FERPA_VW C
WHERE A.INSTITUTION = :1
AND A.STRM = :2
AND NOT EXISTS (SELECT B.EMPLID
FROM PS_STDNT_GRPS_HIST B
WHERE B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_GRPS_HIST B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.INSTITUTION = B_ED.INSTITUTION
AND B.STDNT_GROUP = B_ED.STDNT_GROUP
AND B_ED.EFFDT <= SYSDATE)
AND A.EMPLID = B.EMPLID
AND A.INSTITUTION = B.INSTITUTION
AND B.EFF_STATUS = 'A'
AND substr( B.STDNT_GROUP,1,2) = ('PI','CP'))
AND A.EMPLID = C.EMPLID(+)
AND 'CAMP' = C.E_ADDR_TYPE(+)
AND A.UNT_TAKEN_PRGRSS > 0
FROM PS_STDNT_CAR_TERM A, PS_EMAIL_FERPA_VW C
WHERE A.INSTITUTION = :1
AND A.STRM = :2
AND NOT EXISTS (SELECT B.EMPLID
FROM PS_STDNT_GRPS_HIST B
WHERE B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_GRPS_HIST B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.INSTITUTION = B_ED.INSTITUTION
AND B.STDNT_GROUP = B_ED.STDNT_GROUP
AND B_ED.EFFDT <= SYSDATE)
AND A.EMPLID = B.EMPLID
AND A.INSTITUTION = B.INSTITUTION
AND B.EFF_STATUS = 'A'
AND substr( B.STDNT_GROUP,1,2) = ('PI','CP'))
AND A.EMPLID = C.EMPLID(+)
AND 'CAMP' = C.E_ADDR_TYPE(+)
AND A.UNT_TAKEN_PRGRSS > 0