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
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)
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')
The following returns the result of the PRICE
column multiplied by the QUANTITY
column if both columns are greater than 0. 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))
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.