4.16 IF
@IF
returns one of two values, based upon a condition.
Syntax
@IF (conditional_expression
,nonzero_value
,zero_value
)
Examples
- Example 1
-
The following returns
AMT
only ifAMT
is greater than zero, otherwise zero is returned.AMOUNT_COL = @IF (AMT <= 0, 0, AMT)
- Example 2
-
The following returns
WEST
ifSTATE
isCA
,AZ
orNV
, otherwise returnsEAST
.REGION = @IF (@VALONEOF (STATE, "CA", "AZ", "NV"), "WEST", "EAST")
- Example 3
-
The following returns
NULL
unless bothPRICE
andQUANTITY
are greater than zero.ORDER_TOTAL = @IF (PRICE > 0 AND QUANTITY > 0, PRICE * QUANTITY, @COLSTAT(NULL)
- Example 4
-
The following returns
NULL
unless bothPRICE
andQUANTITY
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)
- Example 5
-
The following returns
NULL
if eitherPRICE
orQUANTITY
isNULL
. When any columns in an expression areNULL
, this is the default action.ORDER_TOTAL = @IF (@COLTEST (PRICE, NULL) OR @COLTEST(QUANTITY, NULL), @COLSTAT(NULL), PRICE * QUANTITY)