Effective date checking

%EffDtCheck

The %EffDtCheck construct expands into an effective date subquery suitable for a Where clause.

Only use the %effdtchk when a table DOES NOT have effective sequence effseq in the key.

Syntax

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

Example

&q = select 'x' from ps_genl_deduction A where A.emplid = :1 and %EffDtCheck(:2 B, A, %CurrentDateIn)
SQLExec(&q, &emplid, Record.GENL_DEDUCTION, &result);

Expands to:

select 'x' from ps_genl_deduction A where A.emplid = '0001' 
   where A.EFFDT = (SELECT MAX(EFFDT) FROM PS_GENL_DEDUCTION 
      WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2 AND. . . 
      AND B.EFFDT <= %DATEIN([current date]))