IIF() mvSQL database function in Jitterbit App Builder

IIF is a shorthand way for writing a CASE expression. CASE is used to provide if-then-else type of logic to SQL. An IIF Statement evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. In App Builder, for all relational databases, IIF Statements can be used for all Rule types you create in the Business Logic Layer.

When working with IIF Statements in App Builder, they also support using both the OR and AND clauses. This article will cover how to use standard IIF statements as well as nested IIF statements.

Summary

Returns one of two values, depending on if the Boolean expression evaluates to true or false.

Syntax

IIF(<condition>, <valueTrue>, <valueFalse>)

Or syntax

The following syntax will return Value 1 if either Condition 1 or Condition 2 are True. Else it returns Value 2:

IIF(Condition 1 OR Condition 2, Value 1, Value 2)

And syntax

The following syntax will return Value 1 if both Condition 1 and Condition 2 are True. Else it returns Value 2:

IIF (Condition 1 AND Condition 2, Value 1, Value 2)

Parameters

condition

A boolean expression that determines which value will be returned.

valuetrue

The value returned if the boolean expression evaluates to true.

valuefalse

The value returned if the boolean expression evaluates to false.

Returns

One of two given values.

Nesting iif statements

IIF Statements can also be nested, where they can be used to support using both OR and AND clauses. Nesting IIF statements would be using an IIF statement in another IIF statement

Nested IIF Statement Example: IIF(Condition 1, Value 1, IIF(Condition 2, Value 2, Value 3))

Examples

IIF(PreferLastName = 1, LastName, FirstName)

Returns

LastName if PreferLastName evaluates to true, otherwise FirstName.