%EffdtCheck

There is a meta-sql element called %EffdtCheck which writes effective date logic for you. For example, I'm writing a view that returns the maximum effective dated and active subject from the subject table.

Here's the meta-SQL:

SELECT INSTITUTION 
, SUBJECT 
, DESCR 
, ACAD_ORG 
, DESCRFORMAL 
FROM PS_SUBJECT_TBL SUBJ 
WHERE %EffdtCheck(SUBJECT_TBL SUBJ_ED, SUBJ, %CurrentDateIn) 
AND SUBJ.EFF_STATUS = 'A'

The line %EffdtCheck(SUBJECT_TBL SUBJ_ED, SUBJ, %CurrentDateIn) expands to get the maximum effective date from the subject table joining on the keys where the effective date is less than or equal to %CurrentDateIn (or whatever date you decide). Note that SUBJ_ED is the alias to the table used for the effective dated join. The second parameter, SUBJ is the alias of the root table. %EffdtDtCheck doesn't include effective sequence or effective status logic so you'll still have to write that the old fashioned way. To check your sql, use the resolve meta-sql functionality in application designer.

So why bother doing this? Well, if the structure of the base table changes and there is a new key, the Meta-SQL will automatically adjust to include the new key. This means you don't have to update the view SQL. It also eliminates the possiblity of missing out joins on keys when you write your effective date logic. Have a look in PeopleBooks for further info. There are a lot of really useful meta-sql elements, just search on meta-sql in the PeopleCode Language reference PeopleBook.

If you use this with the Rowset Fill method, the table alias (parameter 2) is just FILL. This is the alias given automatically PeopleSoft where expanding the SQL in the Fill statement.

Limitations Of %EffDtCheck

The %EffDtCheck construct expands into an effective date subquery suitable for a Where clause. However, it cannot be used with records that contain EFFSEQ as part of the key - in that case, the %EffDtCheck construct is expanded into an effective date subquery INCLUDING the EFFSEQ in the join criteria - which is wrong!

For example, the following SQL:

SELECT *
FROM PS_JOB JOB
WHERE EMPLID = :1
AND EMPL_RCD = :2
AND %EffdtCheck(JOB, JOB_ES, %CurrentDateIn)

Expands into:

SELECT *
FROM PS_JOB JOB
WHERE EMPLID = :1
AND EMPL_RCD = :2
AND JOB_ES.EFFDT=(
SELECT MAX(EFFDT)
FROM PS_JOB JOB
WHERE JOB.EMPLID=JOB_ES.EMPLID
AND JOB.EMPL_RCD=JOB_ES.EMPL_RCD
AND JOB.EFFSEQ=JOB_ES.EFFSEQ /* this shouldn't be used in EFFDT sub-query */
AND JOB.EFFDT<=TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))

As such, when retrieving the current effective row for a record with EFFSEQ as part of the key, the %EffDtCheck construct should not be used. This also applies to other records that contain EFFDT field + additional fields after EFFDT that indicate a parent-child relationship. As stated above, the %EffDtCheck Meta-SQL uses all keys when it's expanded - which may not be what you want in all cases.

Furthermore, as stated above, if the structure of the base table changes and there is a new key, the Meta-SQL will automatically adjust to include the new key. Once again, this may not be the desired behaviour in certain cases.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License