@IF
returns one of two values, based upon a condition.
Syntax
@IF (conditional_expression, nonzero_value, zero_value)
Examples
The following returns AMT
only if AMT
is greater than zero, otherwise zero is returned.
AMOUNT_COL = @IF (AMT <= 0, 0, AMT)
The following returns WEST
if STATE
is CA
, AZ
or NV
, otherwise returns EAST
.
REGION = @IF (@VALONEOF (STATE, "CA", "AZ", "NV"), "WEST", "EAST")
The following returns NULL
unless both PRICE
and QUANTITY
are greater than zero.
ORDER_TOTAL = @IF (PRICE > 0 AND QUANTITY > 0, PRICE * QUANTITY, @COLSTAT(NULL)
The following returns NULL
unless both PRICE
and QUANTITY
are greater than zero. COLSTAT(NULL)
creates a null value in the target column.
ORDER_TOTAL = @IF (PRICE > 0 AND QUANTITY > 0, PRICE * QUANTITY, @COLSTAT(NULL)
The following returns NULL
if either PRICE
or QUANTITY
is NULL
. When any columns in an expression are NULL
, this is the default action.
ORDER_TOTAL = @IF (@COLTEST (PRICE, NULL) OR @COLTEST(QUANTITY, NULL), @COLSTAT(NULL), PRICE * QUANTITY)