mvSQL Database Function - IIF()¶

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 Vinyl, 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 Vinyl, 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.