I have nothing against subqueries, but there are times in the Peoplesoft environment that they are unnecessary. If we can get exactly the same results with simpler, faster SQL, let’s avoid subqueries.
PS_EMPLOYEES
Application Engine program PER099 truncates and repopulates the PS_EMPLOYEES table. Most organizations run it each night. It collects job data, personal data, and prompt table data, and generates one row for each unterminated employee (PS_PER_ORG_ASGN.PER_ORG = ‘EMP’ and PS_JOB.HR_STATUS = ‘A’).
The PS_JOB table contributes many fields to PS_EMPLOYEES, including EMPLID, EMPL_RCD, EFFDT, and EFFSEQ. These four fields are the unique key fields of PS_JOB. The first two of them are the unique key fields of PS_EMPLOYEES.
When we use the EMPLOYEES record in Peoplesoft Query, the program is clever enough to detect the presence of EFFDT and EFFSEQ, but not clever enough to know that they are not key fields and so there can never be multiple values of those fields for a single value of EMPLID and EMPL_RCD. Query offers to write the subqueries to pick a single value of EFFDT and EFFSEQ, and many Query users accept that offer.
That leaves us with a query whose SQL performs an unnecessary three-way self-join of PS_EMPLOYEES. We should find all the queries with that criteria and remove it. Start with this SQL
select OPRID, QRYNAME from PSQRYRECORD where RECNAME = 'EMPLOYEES'
PS_EARNINGS_BAL
COBOL program PSPCNFRM confirms a payroll. Part of that process is to add rows to the balance tables; PS_CHECK_YTD, PS_DEDUCTION_BAL, PS_EARNINGS_BAL, PS_GARN_BAL, and PS_TAX_BALANCE. These tables keep running month-to-date, quarter-to-date, and year-to-date totals of various components of each employee’s paychecks.
The first six fields of these tables are the same, and they constitute part of their unique indices: EMPLID, COMPANY, BALANCE_ID, BALANCE_YEAR, BALANCE_QTR, BALANCE_PERIOD. Any employee might work for multiple tax reporting entities within an organization. They must be differentiated in the PS_COMPANY_TBL. The balance ID lets an organization track year-to-date totals by calendar year (CY) for W-2 reporting and by fiscal year (FY) for internal accounting. The balance year is tricky; it could be the calendar year or the fiscal year. The balance quarter is between one and four. Peoplesoft expects the balance period to correspond to either a calendar month or an accounting month.
It seems natural to find the latest year-to-date rows with subqueries that pick the maximum BALANCE_QTR and the maximum BALANCE_PERIOD in the current BALANCE_YEAR. It was for me, although you, dear reader, may have never made that mistake. A single subquery for the maximum BALANCE_PERIOD will also give us the maximum BALANCE_QTR. We can write the SQL without any mention of BALANCE_PERIOD.
Update Where Exists
Application Engine programs often have SQL statements that update a field of one table with a value returned by a SQL selection. The SQL selection may have some criteria based on the values of the row being updated. Most all tables in the Peoplesoft environment are defined to disallow null values for character fields. That means we need to ensure that we don’t try to update a field with a SQL select that returns no rows. Our SQL update should be in this form,
update PS_TABLENAME
set CHAR_FIELD = (select …)
where exists (select …)
and the “where exists” subquery must only return a row if the “set equals” subquery returns a row. It’s tempting to cut and paste, as follows.
update PS_TABLENAME T
set CHAR_FIELD = (select A.FIELD
from PS_OTHERTABLE A
where A.FIRSTKEY = T.FOREIGNKEY
and A.EFFDT = (select max(EFFDT)
from PS_OTHERTABLE
where FIRSTKEY = A.FIRSTKEY
and EFFDT <= T.DATEFIELD)
where exists (select A.FIELD
from PS_OTHERTABLE A
where A.FIRSTKEY = T.FOREIGNKEY
and A.EFFDT = (select max(EFFDT)
from PS_OTHERTABLE
where FIRSTKEY = A.FIRSTKEY
and EFFDT <= T.DATEFIELD)
However, the “where exists” subquery doesn’t need to check for the existence of the row we’ll use in the “set equals” subquery. If there is any row of PS_OTHERTABLE where FIRSTKEY = T.FOREIGNKEY and EFFDT <= T.DATEFIELD, then we know there will be a current row as of T.DATEFIELD. If we pick an eligible row at random, it is either the current row, or there must be another eligible row with a later date. That second row is either the current row, or there must be another eligible row with a later date. And so on.
Therefore, the SQL we need is a bit simpler, like this.
update PS_TABLENAME T
set CHAR_FIELD = (select A.FIELD
from PS_OTHERTABLE A
where A.FIRSTKEY = T.FOREIGNKEY
and A.EFFDT = (select max(EFFDT)
from PS_OTHERTABLE
where FIRSTKEY = A.FIRSTKEY
and EFFDT <= T.DATEFIELD)
where exists (select 1
from PS_OTHERTABLE A
where A.FIRSTKEY = T.FOREIGNKEY
and A.EFFDT <= T.DATEFIELD)
Recent Comments