Write effective-dated SQL

Effective-dated SQL is where PeopleSoft queries most often go wrong: a subquery that misses a key, a forgotten EFFSEQ, or a department description taken from the wrong row. With AI Assist, the LLM looks up the keys of every record before it writes the query, so the correlations come from your definitions, not from memory.

You’ll use: describe_record, get_translate_values, and optionally run_select.

Ask

Prompt

Write SQL for Oracle that counts active employees by department,
with each department's current description, as of today.

What happens

1. describe_record for JOB returns its keys: EMPLID, EMPL_RCD, EFFDT, and EFFSEQ. Because both EFFDT and EFFSEQ are keys, the current row needs two subqueries: the latest date, then the latest sequence on that date. The field list also includes SETID_DEPT, the setID of the employee’s department.

2. describe_record for DEPT_TBL returns its keys: SETID, DEPTID, and EFFDT. The department’s current row needs its own effective-date subquery, joined through SETID_DEPT.

3. get_translate_values for EMPL_STATUS returns the status codes, so the LLM can decide which ones count as active, and tell you what it decided.

4. run_select, if your administrator allows PS_JOB and PS_DEPT_TBL, runs the query so the LLM can check that the numbers look reasonable before it hands you the SQL.

What you get

Active employees by department (Oracle Database)

SELECT J.DEPTID, D.DESCR, COUNT(DISTINCT J.EMPLID) AS HEADCOUNT
  FROM PS_JOB J
  JOIN PS_DEPT_TBL D
    ON D.SETID = J.SETID_DEPT
   AND D.DEPTID = J.DEPTID
   AND D.EFFDT = (SELECT MAX(D1.EFFDT)
                    FROM PS_DEPT_TBL D1
                   WHERE D1.SETID = D.SETID
                     AND D1.DEPTID = D.DEPTID
                     AND D1.EFFDT <= SYSDATE)
 WHERE J.EFFDT = (SELECT MAX(J1.EFFDT)
                    FROM PS_JOB J1
                   WHERE J1.EMPLID = J.EMPLID
                     AND J1.EMPL_RCD = J.EMPL_RCD
                     AND J1.EFFDT <= SYSDATE)
   AND J.EFFSEQ = (SELECT MAX(J2.EFFSEQ)
                     FROM PS_JOB J2
                    WHERE J2.EMPLID = J.EMPLID
                      AND J2.EMPL_RCD = J.EMPL_RCD
                      AND J2.EFFDT = J.EFFDT)
   AND J.EMPL_STATUS IN ('A', 'L', 'P')
 GROUP BY J.DEPTID, D.DESCR
 ORDER BY J.DEPTID

Along with the query, the LLM explains its choices:

• Active means EMPL_STATUS A (Active), L (Leave of Absence), or P (Leave With Pay). If your organization defines active employees by HR_STATUS instead, say so.
• The JOB subqueries correlate on EMPLID and EMPL_RCD, the keys before EFFDT, and EFFSEQ picks the last change made on the current date.
• COUNT(DISTINCT J.EMPLID) counts people, not jobs. An employee with two active employment records, which EMPL_RCD numbers, would otherwise count twice.
• The department join goes through SETID_DEPT, because departments are keyed by setID.

Check the answer

• Compare with PeopleSoft Query. Build the same count in PeopleSoft Query, which adds effective-date criteria to effective-dated records for you, and compare the totals.
• Spot-check an employee who has several job rows on the same date: open them in Job Data and confirm the query picks the row you’d expect.
• Check the plan before you use the query in a scheduled report, since correlated subqueries on PS_JOB can be expensive on large systems.

Go further

• “Write the same query for SQL Server.”
• “Rewrite it for an Application Engine SQL action, with %CurrentDateIn and %EffDtCheck.”
• “Add each department’s manager.”
• “Count headcount as of the last day of last quarter instead of today.”