IF

@IF returns one of two values, based upon a condition.

Syntax

@IF (conditional_expression, nonzero_value, zero_value)

conditional_expression

The conditional expression.

non-zero_value

The value if the expression is non-zero. A non-zero result is considered TRUE.

zero_value

The value if the expression is zero. A zero result is considered FALSE.

Examples

Example 1

The following returns AMT only if AMT is greater than zero, otherwise zero is returned.

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

Example 2

The following returns WEST if STATE is CA, AZ or NV, otherwise returns EAST.

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

Example 3

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)

Example 4

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)

Example 5

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)