Design

Back Next

iif

Returns one of two values that you specify based on the results of a condition.

Syntax

util:iif(condition, val1 ,val2)

The following table describes the arguments:

Argument	Required/Optional	Description
condition	Required	The condition that you want to evaluate. You can enter any valid transformation expression that evaluates to TRUE or FALSE.
val1	Required	The value that you want to return if the condition is TRUE. The return value is always the data type specified by this argument. You can enter any valid transformation expression, including another iif expression. You can pass any data type except Binary.
val2	Optional	The value that you want to return if the condition is FALSE. You can enter any valid transformation expression, including another iif expression. You can pass any data type except Binary.

The FALSE (val2
) condition in the iif function is not required. If you omit val2
, the function returns one of the following values when the condition is FALSE:

0 if val1
is a Numeric data type.

Empty string if val1
is a String data type.

NULL if val1
is a Date/Time data type.

For example, the following expression does not include a FALSE condition and val1
is a string data type so decode returns an empty string for each row that evaluates to FALSE:

util:iif(SALES > 100, EMP_NAME)

The following table lists some sample values and return values:

SALES	EMP_NAME	RETURN VALUE
150	John Smith	John Smith
50	Pierre Bleu	'' (empty string)
120	Sally Green	Sally Green
NULL	Greg Jones	'' (empty string)

Return Value

val1
if the condition is TRUE.

val2
if the condition is FALSE.

For example, the following expression includes the FALSE condition NULL so decode returns NULL for each row that evaluates to FALSE:

util:iif(SALES > 100, EMP_NAME, NULL)

The following table lists some sample values and return values:

SALES	EMP_NAME	RETURN VALUE
150	John Smith	John Smith
50	Pierre Bleu	NULL
120	Sally Green	Sally Green
NULL	Greg Jones	NULL

iif and data types

When you use iif, the data type of the return value is the same as the data type of the result with the greatest precision.

For example, you have the following expression:

util:iif(SALES < 100, 1, .3333)

The TRUE result (1) is an integer and the FALSE result (.3333) is a decimal. The Decimal data type has a greater precision than the Integer data type. Therefore, the data type of the return value is always a decimal value.

Special uses of iif

Use nested iif statements to test multiple conditions. The following example tests for various conditions and returns 0 if sales is 0 or negative:

util:iif(SALES > 0, util:iif(SALES < 50, SALARY1, util:iif(SALES < 100, SALARY2, util:iif( SALES < 200, SALARY3, BONUS))), 0 )

Miscellaneous functions

Download Guide

Watch

Comments

Cloud Application Integration Homepage

0 COMMENTS

We’d like to hear from you! Log in to comment.

Rename Saved Search

Table of Contents

Design

Design

iif

iif

Syntax

Return Value

iif and data types

Special uses of iif