Informatica IIF Statement reference

Post date: 29-May-2015 09:29:49

Reference:

https://informaticareference.wordpress.com/2012/03/14/iif/

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

Syntax

IIF( condition, value1 [,value2] )

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: 1. 0 if value1 is a Numeric datatype. 2. Empty string if value1 is a String datatype. 3. 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 PowerCenter Integration Service returns an empty string for each row that evaluates to FALSE: IIF( SALES > 100, EMP_NAME )

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 PowerCenter Integration Service returns NULL for each row that evaluates to FALSE: IIF( SALES > 100, EMP_NAME, 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 PowerCenter 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 session in high precision mode and 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 )

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 session performance.