December 6, 2006

SQL for Common HR Queries

One day, while typing "AND JOB.EFFDT = (SELECT MAX(EFFDT)" for about the 10,000th time, it suddenly occurred to me that I had already typed that about 10,000 times and maybe I should save the SQL for future use. Now, I consider myself fairly intelligent and creative, but sometimes the obvious escapes me. Turns out that it really is quicker to cut, paste and modify than to recode from scratch each time!

OK, please stop laughing. My real purpose today is to give a suggestion for what a template for common HRMS queries might look like. So here's a sample you might want to start with. Before using it, it's important that you review it first. For example, if you are not using EMPL_RCD for separate jobs, you should include "EMPL_RCD=0" to improve performance. This sample is best for the older releases (up to 8.3). At newer releases, you might want to drop the EMPLOYMENT reference and decide whether to rely on PERSONAL_DATA or to use the "live" tables instead. Also, decide whether you can rely on the SETID_ fields in JOB, or they are not always accurate (the subject of a previous post) and you prefer to go through the set control tables. And is "ALPS" what you consider a complete list of active codes? Or just rewrite the whole thing in your preferred style. Your mileage may vary—you get the idea. After your review and testing are complete, hopefully you'll have a good base to use for a large percentage of your future ad hoc queries.

SELECT ___
FROM  PS_JOB  JOB
    , PS_PERSONAL_DATA  PER
    , PS_EMPLOYMENT  EMP
    , PS_DEPT_TBL  DEP	
    , PS_LOCATION_TBL  LOC
    , PS_JOBCODE_TBL  JCD
    , XLATTABLE  XL1
WHERE JOB.EFFDT =
      (SELECT MAX(EFFDT)
       FROM   PS_JOB
       WHERE  EMPLID   = JOB.EMPLID
       AND    EMPL_RCD = JOB.EMPL_RCD
       AND    EFFDT   <= SYSDATE)
AND   JOB.EFFSEQ =
      (SELECT MAX(EFFSEQ)
       FROM   PS_JOB
       WHERE  EMPLID   = JOB.EMPLID
       AND    EMPL_RCD = JOB.EMPL_RCD
       AND    EFFDT    = JOB.EFFDT)
AND   PER.EMPLID  = JOB.EMPLID
AND   EMP.EMPLID  = JOB.EMPLID
AND   EMP.EMPL_RCD= JOB.EMPL_RCD
AND   DEP.SETID   = JOB.SETID_DEPT
AND   DEP.DEPTID  = JOB.DEPTID
AND   DEP.EFFDT =
      (SELECT MAX(EFFDT)
       FROM   PS_DEPT_TBL
       WHERE  SETID    = DEP.SETID
       AND    DEPTID   = DEP.DEPTID
       AND    EFFDT   <= SYSDATE)
AND   LOC.SETID   = JOB.SETID_LOCATION
AND   LOC.LOCATION= JOB.LOCATION
AND   LOC.EFFDT =
      (SELECT MAX(EFFDT)
       FROM   PS_LOCATION_TBL
       WHERE  SETID    = LOC.SETID
       AND    LOCATION = LOC.LOCATION
       AND    EFFDT   <= SYSDATE)
AND   JCD.SETID   = JOB.SETID_JOBCODE
AND   JCD.JOBCODE = JOB.JOBCODE
AND   JCD.EFFDT =
      (SELECT MAX(EFFDT)
       FROM   PS_JOBCODE_TBL
       WHERE  SETID    = JCD.SETID
       AND    JOBCODE  = JCD.JOBCODE
       AND    EFFDT   <= SYSDATE)
AND   XL1.FIELDNAME   = '____'
AND   XL1.LANGUAGE_CD = 'ENG'
AND   XL1.FIELDVALUE  = JOB.____
AND   XL1.EFFDT =
      (SELECT MAX(EFFDT)
       FROM   XLATTABLE
       WHERE  FIELDNAME   = XL1.FIELDNAME
       AND    LANGUAGE_CD = XL1.LANGUAGE_CD
       AND    FIELDVALUE  = XL1.FIELDVALUE
       AND    EFFDT      <= SYSDATE)
AND   JOB.EMPL_STATUS IN('A','L','P','S')

Until next time...