Logical functions

Overview

The logical functions covered in this section are:

• ​CHOOSE​
• ​IIF​

CHOOSE

caution

This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

CHOOSE returns an item at the specified index from a list of values in Cinchy. 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.

Syntax

CHOOSE ( index, value1, value2 )

Return Type

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

Arguments

Argument	Description	Example
index	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.	3
value	Values from which the function will select from.	'Manager'

Example 1

The below example chooses from the set of values.

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

Example 2

The below example chooses based on column.

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

Example 3

The blow 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

caution

This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

IFF returns one of two values, depending on if the Boolean expression evaluates TRUE or FALSE.

Syntax

IIF ( condition, true_value, false_value )

Arguments

Argument	Description	Example
condition	A boolean expression which, when evaluated to true will return true_value and to false will return false_value	@Value - 'A'
true_value	The value that will be returned if condition evaluates to true.	'True'
false_value	The value that will be returned if condition evaluates to false.	'False'

Example

The following example returns true if the condition is TRUE, or 10 if the condition is FALSE:

SELECT IIF(@Value = 'A', 'true', 'false')