To determine which records are to be distributed, you define a conditional expression, that is, an expression that is evaluated for each source record and whose result (true or false) determines whether the record is sent to a target table.
The mechanics of defining the expression are very similar to those described for a column expression in the example. The significant difference, however, is that the result of a column expression is a value which is sent to the target, while the result of a conditional expression must be a Boolean “true” or “false”. Records for which the conditional expression is true are sent to the target; records for which it is false are not sent to the target. Additional information about specifying expressions is provided in the System Reference.
Define the expression by doing the following:
-
On the sending server, select the sending table by name, and click Properties on the context menu.
The Sending Table Properties dialog box appears.
-
Accept the Table tab as is, and click the Mapping tab.
-
In the Receiving server and Receiving table lists, select the target that is to receive the records that qualify for the special marketing appeal.
-
Complete the column mapping for this target as usual (for more information, see Select the target columns to include in the data distribution).
-
Select the Gate Condition tab.
You use the workbox on the left of the window to specify the Boolean expression whose results, if true, allow data transmission of the row based on a valid expression for the column.
To associate a table with a Join column, in the Datatype section:
-
Select a table from the Table dropdown.
-
In the Column dropdown, select and insert the join column.
In the Datatype section, use the Column, Operator/Statement, and Method lists to the right to build your expressions.
-
For this exercise, to route records by purchase item, select the ITEM_ID source column name from the Column list, and click Insert.
-
Select the == (is equal) operator from Operator and click Insert.
-
Place your cursor in the window and type in the value that you want to evaluate, in this case, the ITEM_ID of the current version of the item whose purchase you are evaluating. Enclose the value in single quotes because this is a character field.
-
To further qualify the purchased items by the cut-off date for evaluating sales, type “and” and optionally use the Column and Operator lists to add the following clause to the expression:
Sales_Date >= ‘20030801’ -
To verify that your expression is syntactically valid, click the Validate button.
The Expression Handler does the validation and replies with a message about the result. In the event of problems with your expression, diagnostic information is displayed. Also, the Expression Handler log (omnijvm.log, in the Connect CDC’s current working directory) may contain additional information.
-
Click OK once the expression is valid.
-
Optionally, select Keep the target row if UPDATE causes a change in scope to modify how changes in scope are handled.
How Change in Scope works in a Gate Condition
The gate condition in a mapping defines the set of conditions which if true cause data rows to be replicated to the target table. You select Keep the target row if UPDATE causes a change in scope, to suppress a delete on the source target table.
If a mapping has an associated gate condition and the update changes the row’s values so that the update is not sent to the target because the gate condition returns false, a delete operation is sent to the target to delete that row. However, if you select Keep the target row if UPDATE causes a change in scope, the delete operation is suppressed on the source target table.
For example, you can have multiple targets originating from one source where the targets are customers within states so that each target table contains just the customer for one state. Each target mapping would have the following gate condition:
STATE eq ‘MA’: these rows replicate to the Massachusetts tableSTATE eq ‘FL’: these rows replicate to the Florida table
Person A living in MA retires to FL. This generates a source UPDATE that changes the Person A state from MA to FL. Connect CDC recognizes the change in (target) scope and generates a DELETE operation in the “MA” target and an INSERT operation into new “FL” target. If you select Keep the target row if UPDATE causes a change in scope, then when the UPDATE completes , the INSERT into the “FL” target is done and the DELETE from the “MA” target source table is suppressed.
Note: If an INSERT is generated for an update change in scope for a target table where that row still exists (because a prior delete was suppressed), a duplicate key error will occur. To avoid this type of collision, enable unprotected collision resolution for insert operations for the request where the mapping is defined. See Detect collisions for more information. -
Click OK to close the Gate Condition tab.