Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Common Content for Data Integration 10.5
  • All Products

Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions

Transformation Language Reference

Transformation Language Reference

Back Next

IIF

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.
0 COMMENTS
We’d like to hear from you!
William Benton - January 26, 2023

I have an issue using IIF.  The formula is this....

IIF(ISNULL(SUCCESS_RATE)=TRUE,1,0)

SUCCESS_RATE is either a number between 0 and 1, or it is NULL.  I am evaluating 204 records with this formula.  The output of the Expression object becomes only 14 records when I include this formula.  Only the 14 records with a NULL SUCCESS_RATE are included in the output of the object.  I expected those 14 records to give me a value of 1 and the rest to give me a value of 0 from this formula.  Instead they were filtered.  I had to change it to a DECODE to have all 204 records included and the formula perform correctly.  Is this a hidden feature of IIF?  I have used it many times.  Am I inadvertently filtering records in some cases when I use it?

Informatica Documentation Team - January 28, 2023

Hi William,

We’re working to address your comments and will get back to you.

Thanks, Informatica Documentation team

Informatica Documentation Team - January 31, 2023

Hi William,

We recommend that you contact Informatica Global Customer Support to address this query because this query is related to a specific use case based on product behavior.

Thanks, Informatica Documentation team