April 18, 2004

EFFDT and EFF_STATUS

Last week I discussed using all of the keys in a subselect to increase performance. Today I'd like to talk about another very common item related to subselects.

Many of the tables we work with every day are effective-dated. The code nearly always follows the same pattern, as shown in this sample code from last week:

     SELECT ...
     FROM  PS_JOB       JOB
         , PS_DEPT_TBL  DEP
     WHERE ...
     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 <= ...
           )

Clear enough. But notice that I'm being just a little vague here. What's the "..." being compared with EFFDT in this example?

Many times we will simply code SYSDATE or GETDATE() (depending on the type of database) or use a PeopleSoft construct such as %CurrentDateIn without a second thought. But there is a decision to be made here, and in some cases it can be important. Do we want to select the DEPT_TBL row as of the JOB row's effective date, or do we want the current DEPT_TBL row? Suppose that we are trying to retrieve only the DESCR. Do we want the department name as it exists today, or as it existed when the JOB row was created?

In many cases it doesn't matter much. But we should consider the purpose of the description (as in this example). Why is it on the report at all? What information are we trying to convey? Does the person receiving the report expect to see the current name of the department?

Although a description might not seem very important, there are other cases to consider. Suppose that instead of a description we are retrieving financial information. Now the choice of an effective date can be critical. Also, what happens when we need to retrieve a row from one effective-dated table to obtain a key to yet another effective-dated table? I've found that this situation tends to arise while traversing the benefits setup tables, for example.

If your report always runs as of the current date, you might be safe in coding under that assumption. On the other hand, if you provide a run control option for an as-of date (or "from" and "through" dates), be sure that your choice in coding the effective date logic makes sense based on the run control date the user enters.

One mistake that is easy to avoid is the incorrect use of EFF_STATUS. I occasionally see code such as this:

     SELECT ...
     FROM  PS_JOB       JOB
         , PS_DEPT_TBL  DEP
     WHERE ...
     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 <= ...
            AND    EFF_STATUS = 'A'  -- WRONG
           )

In almost every case, this is inappropriate. EFF_STATUS should not be checked as shown above.

Notice that EFF_STATUS is not a key. That should be a clue to us as we are writing subselects. The main purpose of EFF_STATUS is to help PeopleTools (specifically, the component processor) select the available rows from prompt tables. When a department (for example) is closed down, we no longer want users to be able to select it. But that doesn't extend to our coding when we are producing reports. If an inactive department still contains employees--and there is nothing to prevent this--we certainly want to select the department description. More importantly, code such as this could cause a join to fail, meaning that the employees in the inactive department would simply disappear from the report. Whether the department or other entity is currently active or inactive, it still has a name and other attributes that we can select.

Until next time...