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'
%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?
- 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 reduces the possiblity of missing out joins on keys when you write your effective date logic.
%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)
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.