Please enable JavaScript to view this site.

The @BEFORE function returns the before image of a source field or column following an Update operation. For deletes and inserts it return the current value. It also provides access to the before key column values of relational targets to support Key Changes.

@BEFORE does not provide access to the raw data of the before image. The before image has already been processed using default and user specified data rules. This includes data type and EXTTYPE validation, error processing and code page translations. @BEFORE is most often used to implement conditional business logic based on a change to the value of a particular source field/column. It is also used to support changes to the value of a column when replicating changes to a Relational target when using the APPLY command to write to the Target datastore.

Note: @BEFORE replaces both the CDCBEFORE and the MAP_BEFORE functions and is best used when referencing individual source fields rather than all the Before image source fields in a CDC record where SETIMAGE may be a better choice.

Category

Changed Data Capture

Syntax

<source_description>.<source_field>@BEFORE | <target_description>.<target_field>@BEFORE

 

Parameter Descriptions

<source_description>.<source_field>  | <target_description>.<target_field>  -  The source or target (Key ONLY) field name.

Example 1

On a CDC Update that may include a change to a Relational target key column, provide the "before" value of the target keys to insure that the update replaces the existing row. When Compensation is specified, MAPBEFOER ensures that the new data is not inserted".

IF CDCOP(CDCIN) = 'U'

{

   T_EMPLOYEE.SITE_CD@BEFORE       = EMPLOYEE.SITE_CD@BEFORE

   T_EMPLOYEE.STATE_CD@BEFORE      = EMPLOYEE.STATE_CD@BEFORE

   T_EMPLOYEE.OFF_ZIP_CD@BEFORE    = EMPLOYEE.OFF_ZIP_CD@BEFORE

}

-- EMPLOYEE.EMP_NO in this example cannot be changed and therefore is not specified above.

 

Example 2

When a client’s account status changes from OPENED to CLOSED. Retrieve the before image content of source field SRC_FLD1 from a changed data capture record and map the result to target field T_PREV_SRC_FLD1.

T_PREV_SRC_FLD1  = SRC_FLD1@BEFORE

 

Example 3

Call a special process if the SOURCE.STATUS field changes from OPENED to CLOSED.

IF SOURCE.STATUS@before = 'OPEN' AND SOURCE.STATUS = 'CLOSED'

{

   CALLPROC(CLOSING)

 

}

Example 4

On an Update, determine if the source field SRC_FLD1 has changed and set the variable CHGFLAG to "Y"  if it has otherwise set the value to "N".

CASE

WHEN SRC_FLD1@BEFORE = SRC_FLD1

{

   CHGFLAG = 'N'

}

OTHERWISE

{

  CHGFLAG  =  'Y'

}

Note, this can also be accomplished using the FLDCHG function.