I'm getting back more than I want. I need to get the latest row in the PS_ACAD_STDNG_ACTN that has the academic standing code for students.
I thought if I max the effdt, strm, and effseq I would get back only one row. Especially effdt since a academic status is hardly ever updated on the same date.
PS_NTSR_GF_STUFILE will have multiple emplid's for students taking classes. PS_ACAD_STDNG_ACTN should have the last standing status for each student..(PRO = Probation, DIS = Dismissed).
UPDATE PS_NTSR_GF_STUFILE a
SET a.NTSR_GF_ENRL_STAT = nvl((
FROM PS_GBSA_DTL b, PS_ACAD_STDNG_ACTN c
WHERE c.ACAD_STNDNG_STAT = b.GBSA_VALUE
AND b.INSTITUTION = 'DL773'
AND c.EFFDT = (
FROM PS_ACAD_STDNG_ACTN f
WHERE a.EMPLID = f.EMPLID
AND a.ACAD_CAREER = f.ACAD_CAREER
AND f.EFFDT <= SYSDATE)
AND c.STRM = (
FROM PS_ACAD_STDNG_ACTN d
WHERE a.EMPLID = d.EMPLID
AND a.ACAD_CAREER = d.ACAD_CAREER)
AND c.EFFSEQ = (
FROM PS_ACAD_STDNG_ACTN e
WHERE a.EMPLID = e.EMPLID
AND a.ACAD_CAREER = e.ACAD_CAREER)
AND c.INSTITUTION = (
WHERE INSTITUTION = 'DL773'
), ' ');