The %EffDtCheck construct expands into an effective date subquery suitable for a Where clause. The value for as_of_date is automatically wrapped in %DateIn unless as_of_date is already wrapped in %DateIn or refers to other database columns.

Syntax
%EffDtCheck(recordname [correlation_id1], correlation_id2, as_of_date)

Example:
The following example uses correlation IDs:
SELECT A.DEPTID
FROM PS_DEPT_TBL A
WHERE
%EffDtCheck(DEPT_TBL B, A, %CurrentDateIn)
AND A.EFF_STATUS = ‘A’

This example code resolves into the following:
SELECT A.DEPTID
FROM PS_DEPT_TBL A
WHERE
A.EFFDT = 
(SELECT MAX(B.EFFDT)
FROM DEPT_TBL B
WHERE
A.SETID = B.SETID
AND A.DEPTID = B.DEPTID
AND B.EFFDT <=%CurrentDateIn)
AND A.EFF_STATUS = ‘A’

It looks to be a very smart solution!!!

Advertisements