Logical Functions

1. Overview

The logical functions covered in this section are:

• ​CHOOSE​

• ​IIF​

CHOOSE

The CHOOSE function returns an item at the specified index from a list of values in Cinchy.

Syntax

CHOOSE ( index, val_1, val_2 [, val_n ] )

Arguments

index Is an integer expression that represents a 1-based index into the list of the items following it.

If the provided index value has a numeric data type other than int, then the value is implicitly converted to an integer. If the index value exceeds the bounds of the array of values, then CHOOSE returns null.

Return Types

Returns the data type with the highest precedence from the set of types passed to the function.

Remarks

CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.

Example 1

Simple CHOOSE example

SELECT CHOOSE( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result

Example 2

Simple CHOOSE example based on column

SELECT
    [Category ID],
    CHOOSE ([Category ID], 'A', 'B', 'C', 'D', 'E') AS Expression1
FROM
    [Production].[Product Category]

Example 3

CHOOSE in combination with MONTH

The following example returns the season in which a user was added to Cinchy. The MONTH function is used to return the month value from the column HireDate.

SELECT
    [Display Name],
    [Created],
    CHOOSE(
        MONTH([Created]),
        'Winter',
        'Winter',
        'Spring',
        'Spring',
        'Spring',
        'Summer',
        'Summer',
        'Summer',
        'Autumn',
        'Autumn',
        'Autumn',
        'Winter'
    ) AS Quarter
FROM
    [Cinchy].[Users]
WHERE
    [Deleted] IS NULL
    AND YEAR([Created]) > 2005
ORDER BY
    YEAR([Created])

IIF

IFF returns one of two values which is depending on if the Boolean expression evaluates TRUE or FALSE in the Cinchy.

Syntax

IIF ( condition, true_value, false_value )

Arguments

boolean_expression A valid Boolean expression.

If this argument is not a Boolean expression, then a syntax error is raised.

true_value Value to return if boolean_expression evaluates to true.

false_value Value to return if boolean_expression evaluates to false.

Return Types

Returns the data type with the highest precedence from the types in true_value and false_value.

Remarks

IIF is a second version of writing a CASE expression. It evaluates the Boolean expression which was passed as the first argument and then returns either TRUE or FALSE based on the result of the evaluation. Thetrue_valueis returned if the Boolean expression is TRUE, and thefalse_valueis returned if the Boolean expression is FALSE or unknown.

Example

Return 5 if the condition is TRUE, or 10 if the condition is FALSE:

DECLARE @Value CHAR(1) = 'B'
SELECT IIF(@Value = 'A',5,10)