%EffdtCheck

There is a very handy 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, SUBJis 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?

1. 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.
2. It reduces the possiblity of missing out joins on keys when you write your effective date logic.

Limitations

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.