Transformation Guide

Back Next

Update Strategy Expressions

Frequently, the update strategy expression uses the IIF or DECODE function from the transformation language to test each row to see if it meets a particular condition. If it does, you can then assign each row a numeric code to flag it for a particular database operation. For example, the following IIF statement flags a row for reject if the entry date is after the apply date. Otherwise, it flags the row for update:

IIF( ( ENTRY_DATE > APPLY_DATE), DD_REJECT, DD_UPDATE )

To create an Update Strategy transformation:

In the Mapping Designer, add an Update Strategy transformation to a mapping.

Click Layout > Link Columns.

Drag all ports from another transformation representing data you want to pass through the Update Strategy transformation.

In the Update Strategy transformation, the Designer creates a copy of each port you drag. The Designer also connects the new port to the original port. Each port in the Update Strategy transformation is a combination input/output port.

Normally, you would select all of the columns destined for a particular target. After they pass through the Update Strategy transformation, this information is flagged for update, insert, delete, or reject.

Open the Update Strategy transformation and rename it.

The naming convention for Update Strategy transformations is UPD_
TransformationName
.

Click the Properties tab.

Click the button in the Update Strategy Expression field.

The Expression Editor appears.

Enter an update strategy expression to flag rows as inserts, deletes, updates, or rejects.

Validate the expression and click OK.

Click OK.

Connect the ports in the Update Strategy transformation to another transformation or a target instance.

Rename Saved Search

Table of Contents

Transformation Guide

Transformation Guide

Update Strategy Expressions

Update Strategy Expressions