Preface
The Transformation Language
Constants
Operators
Variables
- Built-in Variables
  - SYSDATE
    - Example
- Local Variables
Dates
Functions
- Function Categories
  - Aggregate Functions
  - Aggregate Functions and Nulls
    - Filter Conditions
  - Character Functions
  - Complex Functions
  - Conversion Functions
  - Data Cleansing Functions
  - Date Functions
  - Encoding Functions
  - Financial Functions
  - Numeric Functions
  - Scientific Functions
  - Special Functions
  - String Functions
  - Test Functions
  - Window Functions
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- ANY
- ARRAY
- ASCII
- AVG
- CAST
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COLLECT_LIST
- COLLECT_MAP
- COMPRESS
- CONCAT
- CONCAT_ARRAY
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CREATE_TIMESTAMP_TZ
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- EXTRACT_STRUCT
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GET_TIMEZONE
- GET_TIMESTAMP
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- ISNULL
- IS_DATE
- IS_NUMBER
- IS_SPACES
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAP
- MAP_FROM_ARRAYS
- MAP_KEYS
- MAP_VALUES
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PARSE_JSON
- PARSE_XML
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- RESPEC
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SIGN
- SIN
- SINH
- SIZE
- SOUNDEX
- SQL_LIKE
- SQRT
- STDDEV
- STRUCT
- STRUCT_AS
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TIME_RANGE
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_DECIMAL38
- TO_FLOAT
- TO_INTEGER
- TO_TIMESTAMP_TZ
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- UUID4
- UUID_UNPARSE
- VARIANCE

Transformation Language Reference

10.5.1
- 10.5.6
- 10.5.4
- 10.5
- 10.4.0

Back Next

IIF

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

Syntax

IIF( condition, value1 [,value2] )

The following table describes the arguments for this command:

Argument	Required/ Optional	Description
condition	Required	The condition you want to evaluate. You can enter any valid transformation 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 transformation 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 transformation 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:

0 if value1
is a Numeric datatype.

Empty string if value1
is a String datatype.

NULL if value1
is a Date/Time datatype.

For example, the following expression does not include a FALSE condition and value1
is a string datatype so the

Data Integration Service

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 the

Data Integration Service

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 and data movement mode of the

Data Integration Service

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 you run a mapping in high precision mode and at least one result is Double, the datatype of the return value is Double.

IIF and Complex Data Types

You can use IIF to return an array or a struct, or elements from the array or struct.

For example, you have the following array:

names = ['John', 'Kevin', 'Laura']

You can use the following expression to return one of the values in the array:

IIF( SIZE(names) > 2, names[2], names[0] )

RETURN VALUE
: 'Laura'

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)

You can often use a Filter transformation instead of IIF to maximize performance.

Rename Saved Search

Table of Contents

Transformation Language Reference

Transformation Language Reference

IIF

IIF

Syntax

Return Value

IIF and Datatypes

IIF and Complex Data Types

Special Uses of IIF

Alternative to IIF