@IF

Use the @IF function to return one of two values, based on a condition. You can use the @IF function with other Oracle GoldenGate functions to begin a conditional argument that tests for one or more exception conditions. You can direct processing based on the results of the test. You can nest @IF statements, if needed.

Syntax

@IF (condition, value_if_non-zero, value_if-zero)

condition

A valid conditional expression or Oracle GoldenGate function. Use numeric operators (such as =, > or <) only for numeric comparisons. For character comparisons, use one of the character-comparison functions.

value_if_non-zero

Non-zero is considered true.

value_if_zero

Zero (0) is considered false.

Examples

Example 1

The following returns an amount only if the AMT column is greater than zero; otherwise zero is returned.

AMOUNT_COL = @IF (AMT > 0, AMT, 0)

Example 2

The following returns WEST if the STATE column is CA, AZ or NV; otherwise it returns EAST.

REGION = @IF (@VALONEOF (STATE, 'CA', 'AZ', 'NV'), 'WEST', 'EAST')

Example 3

The following returns the result of the PRICE column multiplied by the QUANTITY column if both columns are greater than

1. Otherwise, the @COLSTAT (NULL) function creates a NULL value in the target column.

ORDER_TOTAL = @IF (PRICE > 0 AND QUANTITY > 0, PRICE * QUANTITY,
@COLSTAT (NULL))

Example 4

The following example demonstrates a nested @IF statement. In the example, if the QUANTITY is more than 10, then the item price is 90% of thePRICE.

ORDER_TOTAL = @IF (PRICE > 0 AND QUANTITY > 0, @IF (QUANTITY > 10, (PRICE * 0.9) * QUANTITY, PRICE * QUANTITY), @COLSTAT(NULL))

Note: When enclosed in parenthesis (), Oracle GoldenGate column mapping function expects numeric results. The column value must be specified using single quotes.