%EffDtCheck meta-SQL element

Syntax

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

Description

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.

Note:

This meta-SQL construct is not implemented for COBOL.

%EffDtCheck only works with effective dates. It does not take effective sequence numbers (EFFSEQ) into account. It also does not do effective-status (EFF_STATUS) checking.

Parameters

Parameter	Description
recordname	Specify the record name to use as the record in the effective-date checking. This can be a bind variable, a record object, or a record name in the form recname. You cannot specify a RECORD. recname, a record name in quotation marks, or a table name. Note: If you specify a bind variable, it should refer to a record object, not a string variable.
correlation_id1	(Optional) Specify the letter used inside the effective-dating subselect. If this parameter isn't specified, recordname is used.
correlation_id2	Specify the letter already assigned to the main record in the From clause of the SQL statement.
as_of_date	Specify the date to use in the effective date. This can be a bind variable, a variable, or a hard-coded date. 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.

Example

The following is a generic code sample:

SELECT. . .    
   FROM. . . 
      WHERE %EffDtCheck(recordname correlation_id, as_of_date)

The example code resolves into the following:

SELECT . . . 
   FROM. . . 
   WHERE correlation_id.EFFDT = (SELECT MAX(EFFDT) FROM recordname 
      WHERE recordname.KEYFIELD1 = correlation_id.KEYFIELD1 
      AND recordname.KEYFIELD2 = correlation_id.KEYFIELD2 
      AND. . . 
      AND recordname.EFFDT <= %DATEIN(as_of_date))

In the following example, &Date has the value of 01/02/1998. The example &Rec object has an EFFDT key field.

SQLExec("SELECT FNUM FROM PS_REC A WHERE %EffDtCheck(:1, A, :2)", &Rec, &Date);

This example code resolves into the following:

"Select FNUM from PS_REC A where EFFDT = (select MAX(EFFDT)  
from PS_REC  
   where PS_REC.FNUM = A.FNUM  
   and PS_REC.EFFDT <= %DateIn('1998-01-02') )"

The following example uses correlation IDs:

SELECT A.DEPTID 
FROM %Table(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 %Table(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'