Function Reference > Functions > IIF
  

IIF

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

Syntax

IIF( condition, value1 [,value2] )
Argument
Required/
Optional
Description
condition
Required
The condition you want to evaluate. You can enter any valid expression that evaluates to TRUE or FALSE.
value1
Required
Any datatype except Binary. The value you want to return if the condition is TRUE. The return value is always the datatype specified by this argument. You can enter any valid expression, including another IIF expression.
value2
Optional
Any datatype except Binary. The value you want to return if the condition is FALSE. You can enter any valid expression, including another IIF expression.
Unlike conditional functions in some systems, the FALSE (value2) condition in the IIF function is not required. If you omit value2, the function returns the following when the condition is FALSE:
For example, the following expression does not include a FALSE condition and value1 is a string datatype so Data Integration returns an empty string for each row that evaluates to FALSE:
IIF( SALES > 100, EMP_NAME )
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

value1 if the condition is TRUE.
value2 if the condition is FALSE.
For example, the following expression includes the FALSE condition NULL so Data Integration returns NULL for each row that evaluates to FALSE:
IIF( SALES > 100, EMP_NAME, NULL )
SALES
EMP_NAME
RETURN VALUE
150
John Smith
John Smith
50
Pierre Bleu
NULL
120
Sally Green
Sally Green
NULL
Greg Jones
NULL
If the data contains multibyte characters and the condition argument compares string data, the return value depends on the code page of the Secure Agent that runs the task.

IIF and Datatypes

When you use IIF, the datatype of the return value is the same as the datatype of the result with the greatest precision.
For example, you have the following expression:
IIF( SALES < 100, 1, .3333 )
The TRUE result (1) is an integer and the FALSE result (.3333) is a decimal. The Decimal datatype has greater precision than Integer, so the datatype of the return value is always a Decimal.
When at least one result is Double, the datatype of the return value is Double.

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:
IIF( SALES > 0, IIF( SALES < 50, SALARY1, IIF( SALES < 100, SALARY2, IIF( SALES < 200, SALARY3, BONUS))), 0 )
You can make this logic more readable by adding comments:
IIF( SALES > 0,
   --then test to see if sales is between 1 and 49:
      IIF( SALES < 50,
         --then return SALARY1
            SALARY1,
         --else test to see if sales is between 50 and 99:
            IIF( SALES < 100,
               --then return
                  SALARY2,
               --else test to see if sales is between 100 and 199:
                  IIF( SALES < 200,
                     --then return
                        SALARY3,
                     --else for sales over 199, return
                        BONUS)
                  )
           ),
   --else for sales less than or equal to zero, return
      0)
Use IIF in update strategies. For example:
IIF( ISNULL( ITEM_NAME ), DD_REJECT, DD_INSERT)

Alternative to IIF

Use DECODE instead of IIF in many cases. DECODE may improve readability. The following shows how you use DECODE instead of IIF using the first example from the previous section:
DECODE( TRUE,
   SALES > 0 and SALES < 50, SALARY1,
   SALES > 49 AND SALES < 100, SALARY2,
   SALES > 99 AND SALES < 200, SALARY3,
   SALES > 199, BONUS)