PS Query & SQL

Hi Pheng,

It looks like the issue you're encountering is related to how effective-dated rows are being pulled in your query specifically, how MAX(EFFDT) and MAX(EFFSEQ) subqueries are used. The current logic is likely filtering for only the most recent record overall for each student, instead of the correct record as of the term you're prompting for.

This is a common issue when querying historical or future data in PeopleSoft. Many effective-dated tables (like PS_ACAD_PLAN, PS_ACAD_PROG, PS_ACAD_STDNG_ACTN, etc.) store a student's progression over time, but unless you scope the effective-dated logic to a particular term, the query defaults to whatever is most current. That's why you're only getting the prior term and it's likely the most recent one with effective rows before today.

Let's take PS_ACAD_PLAN as an example. Instead of this:

AND A.EFFDT = (

  SELECT MAX(EFFDT)

  FROM PS_ACAD_PLAN

  WHERE EMPLID = A.EMPLID

    AND ACAD_CAREER = A.ACAD_CAREER

    AND STDNT_CAR_NBR = A.STDNT_CAR_NBR

)

Use logic like this instead:

AND A.EFFDT = (

  SELECT MAX(EFFDT)

  FROM PS_ACAD_PLAN A_ED

  WHERE A.EMPLID = A_ED.EMPLID

    AND A.ACAD_CAREER = A_ED.ACAD_CAREER

    AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR

    AND A_ED.EFFDT <= (

      SELECT MIN(TERM_BEGIN_DT)

      FROM PS_TERM_TBL

      WHERE STRM = B.STRM

        AND INSTITUTION = B.INSTITUTION

    )

)

AND A.EFFSEQ = (

  SELECT MAX(EFFSEQ)

  FROM PS_ACAD_PLAN A_ES

  WHERE A.EMPLID = A_ES.EMPLID

    AND A.ACAD_CAREER = A_ES.ACAD_CAREER

    AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR

    AND A.EFFDT = A_ES.EFFDT

)

This version ensures you're pulling the latest row as of the beginning of the term passed in via your prompt. It corrects the issue where the query would otherwise skip over earlier or future term records just because their effective dates aren't the latest.

I recommend reviewing all the effective-dated joins in your query and replace MAX(EFFDT) logic that is scoped to SYSDATE or global MAX() with logic tied to the STRM you're prompting for. This includes tables like:

• PS_ACAD_PLAN
• PS_ACAD_PROG
• PS_ACAD_STDNG_ACTN
• PS_RIT_SR_APPL_VW
• PS_STDNT_GRPS_HIST

By aligning the effective-dated logic with the term context, you should start seeing historical and future rows returned as expected.

Daniel's response is a great way to ensure pulling the Max Effective Dated rows as of a Term Begin Date.  I'd like to offer a few other suggestions:

1. First thing I would do is move the join to PS_TERM_TBL out of the subquery and place at the main level, joining on INSTITUTION, ACAD_CAREER, and STRM from PS_STDNT_CAR_TERM.  This allows a full join based on the Acad Career they are Term Activated in and only requires one version of PS_TERM_TBL versus having to use in multiple subqueries for other EFFDT clauses.
2. I also recommend not using PS_ACAD_PLAN as the table for looking up Max EFFDT.  PS_ACAD_PLAN is a child table and since plans can change, you may not want to be including it in your results for a Term that it is no longer associated with the Students Program/Plan stack.  PS Query will always try to use Max Effdt clause for the table the EFFDT is on.  But, when Parent/Child tables are involved, it is always best to use the Parent table.  In this case, PS_ACAD_PROG.  That ensures you are only pulling in the Acad_Plan values that are truly in effect as of a given date and not including ones they may have dropped as of a newer effective date.
3. Since Academic Program/Plan stack can change mid-term, I would determine if you are only looking for the status as of the beginning of a term or the most current status within a term.  If looking for the latter, I tend to use a Case Statement similar to this within the SubQuery assuming PS_TERM_TBL using an alias of T was joined outside the subquery already:  

AND A_ED.EFFDT <= CASE WHEN T.TERM_END_DT <= SYSDATE THEN T.TERM_END_DT

                                                      WHEN T.TERM_BEGIN_DT >= SYSDATE THEN T.TERM_BEGIN_DT

                                                      ELSE SYSDATE END

This statement will pull the latest Effective Date that was in play for a term for any past terms up to the end of that term, use the most current effective dated row in place as of the start of a term for any future terms, and use the current date for any currently active terms.

Hi Pheng,

It looks like the issue you're encountering is related to how effective-dated rows are being pulled in your query specifically, how MAX(EFFDT) and MAX(EFFSEQ) subqueries are used. The current logic is likely filtering for only the most recent record overall for each student, instead of the correct record as of the term you're prompting for.

This is a common issue when querying historical or future data in PeopleSoft. Many effective-dated tables (like PS_ACAD_PLAN, PS_ACAD_PROG, PS_ACAD_STDNG_ACTN, etc.) store a student's progression over time, but unless you scope the effective-dated logic to a particular term, the query defaults to whatever is most current. That's why you're only getting the prior term and it's likely the most recent one with effective rows before today.

Let's take PS_ACAD_PLAN as an example. Instead of this:

AND A.EFFDT = (

  SELECT MAX(EFFDT)

  FROM PS_ACAD_PLAN

  WHERE EMPLID = A.EMPLID

    AND ACAD_CAREER = A.ACAD_CAREER

    AND STDNT_CAR_NBR = A.STDNT_CAR_NBR

)

Use logic like this instead:

AND A.EFFDT = (

  SELECT MAX(EFFDT)

  FROM PS_ACAD_PLAN A_ED

  WHERE A.EMPLID = A_ED.EMPLID

    AND A.ACAD_CAREER = A_ED.ACAD_CAREER

    AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR

    AND A_ED.EFFDT <= (

      SELECT MIN(TERM_BEGIN_DT)

      FROM PS_TERM_TBL

      WHERE STRM = B.STRM

        AND INSTITUTION = B.INSTITUTION

    )

)

AND A.EFFSEQ = (

  SELECT MAX(EFFSEQ)

  FROM PS_ACAD_PLAN A_ES

  WHERE A.EMPLID = A_ES.EMPLID

    AND A.ACAD_CAREER = A_ES.ACAD_CAREER

    AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR

    AND A.EFFDT = A_ES.EFFDT

)

This version ensures you're pulling the latest row as of the beginning of the term passed in via your prompt. It corrects the issue where the query would otherwise skip over earlier or future term records just because their effective dates aren't the latest.

I recommend reviewing all the effective-dated joins in your query and replace MAX(EFFDT) logic that is scoped to SYSDATE or global MAX() with logic tied to the STRM you're prompting for. This includes tables like:

• PS_ACAD_PLAN
• PS_ACAD_PROG
• PS_ACAD_STDNG_ACTN
• PS_RIT_SR_APPL_VW
• PS_STDNT_GRPS_HIST

By aligning the effective-dated logic with the term context, you should start seeing historical and future rows returned as expected.

Thanks for the helpful suggestions Daniel and Mark. Upon closer look at the query I did find that I did an incorrect join on the instructor view table and did not do a left join the academic standing table.  Since this query as an update to an older query written in 2018, I decided to leave the first set of record tables as is after finding the incorrect joins. 

Mark - I incorporated your suggestion regarding the CASE statement and this further enhanced the query as you noted in your reply. For instance, this is what I observed when validating a student. The query used to pull in the most recent program plan and with the CASE statement, it is now pulling in the program plan based on the most recent effective date for the specified term.

Older Version Query-

Revised Version of Query-

Daniel's response is a great way to ensure pulling the Max Effective Dated rows as of a Term Begin Date.  I'd like to offer a few other suggestions:

1. First thing I would do is move the join to PS_TERM_TBL out of the subquery and place at the main level, joining on INSTITUTION, ACAD_CAREER, and STRM from PS_STDNT_CAR_TERM.  This allows a full join based on the Acad Career they are Term Activated in and only requires one version of PS_TERM_TBL versus having to use in multiple subqueries for other EFFDT clauses.
2. I also recommend not using PS_ACAD_PLAN as the table for looking up Max EFFDT.  PS_ACAD_PLAN is a child table and since plans can change, you may not want to be including it in your results for a Term that it is no longer associated with the Students Program/Plan stack.  PS Query will always try to use Max Effdt clause for the table the EFFDT is on.  But, when Parent/Child tables are involved, it is always best to use the Parent table.  In this case, PS_ACAD_PROG.  That ensures you are only pulling in the Acad_Plan values that are truly in effect as of a given date and not including ones they may have dropped as of a newer effective date.
3. Since Academic Program/Plan stack can change mid-term, I would determine if you are only looking for the status as of the beginning of a term or the most current status within a term.  If looking for the latter, I tend to use a Case Statement similar to this within the SubQuery assuming PS_TERM_TBL using an alias of T was joined outside the subquery already:  

AND A_ED.EFFDT <= CASE WHEN T.TERM_END_DT <= SYSDATE THEN T.TERM_END_DT

                                                      WHEN T.TERM_BEGIN_DT >= SYSDATE THEN T.TERM_BEGIN_DT

                                                      ELSE SYSDATE END

This statement will pull the latest Effective Date that was in play for a term for any past terms up to the end of that term, use the most current effective dated row in place as of the start of a term for any future terms, and use the current date for any currently active terms.

Hi Pheng,

It looks like the issue you're encountering is related to how effective-dated rows are being pulled in your query specifically, how MAX(EFFDT) and MAX(EFFSEQ) subqueries are used. The current logic is likely filtering for only the most recent record overall for each student, instead of the correct record as of the term you're prompting for.

This is a common issue when querying historical or future data in PeopleSoft. Many effective-dated tables (like PS_ACAD_PLAN, PS_ACAD_PROG, PS_ACAD_STDNG_ACTN, etc.) store a student's progression over time, but unless you scope the effective-dated logic to a particular term, the query defaults to whatever is most current. That's why you're only getting the prior term and it's likely the most recent one with effective rows before today.

Let's take PS_ACAD_PLAN as an example. Instead of this:

AND A.EFFDT = (

  SELECT MAX(EFFDT)

  FROM PS_ACAD_PLAN

  WHERE EMPLID = A.EMPLID

    AND ACAD_CAREER = A.ACAD_CAREER

    AND STDNT_CAR_NBR = A.STDNT_CAR_NBR

)

Use logic like this instead:

AND A.EFFDT = (

  SELECT MAX(EFFDT)

  FROM PS_ACAD_PLAN A_ED

  WHERE A.EMPLID = A_ED.EMPLID

    AND A.ACAD_CAREER = A_ED.ACAD_CAREER

    AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR

    AND A_ED.EFFDT <= (

      SELECT MIN(TERM_BEGIN_DT)

      FROM PS_TERM_TBL

      WHERE STRM = B.STRM

        AND INSTITUTION = B.INSTITUTION

    )

)

AND A.EFFSEQ = (

  SELECT MAX(EFFSEQ)

  FROM PS_ACAD_PLAN A_ES

  WHERE A.EMPLID = A_ES.EMPLID

    AND A.ACAD_CAREER = A_ES.ACAD_CAREER

    AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR

    AND A.EFFDT = A_ES.EFFDT

)

This version ensures you're pulling the latest row as of the beginning of the term passed in via your prompt. It corrects the issue where the query would otherwise skip over earlier or future term records just because their effective dates aren't the latest.

I recommend reviewing all the effective-dated joins in your query and replace MAX(EFFDT) logic that is scoped to SYSDATE or global MAX() with logic tied to the STRM you're prompting for. This includes tables like:

• PS_ACAD_PLAN
• PS_ACAD_PROG
• PS_ACAD_STDNG_ACTN
• PS_RIT_SR_APPL_VW
• PS_STDNT_GRPS_HIST

By aligning the effective-dated logic with the term context, you should start seeing historical and future rows returned as expected.