Control Flow

IF-THEN-ELSE

Evaluate different expressions whether the condition is true or false. If the condition is null, the result is null.

Syntax:

IF <bool expression> [THEN] <expression> ELSE <expression>

For example:

if (true) then "a" else "b" \\ "a"
if (false) then "a" else "b" \\ "b"
if (null) then "a" else "b" \\ null

if (true) "a" else "b"

IFF

IFF is a shorthand version of IF-THEN-ELSE.

Syntax:

IFF(<bool expression>, <then expression>, <else expression>)

For example:

IFF(true, "a", "b") \\ "a"
IFF(false, "a", "b") \\ "b"
IFF(null, "a", "b") \\ null

Attention

IFF not currently implemented.

CASE

Evaluates a list of conditions and returns one of multiple possible result expressions.

Syntax:

CASE <expression>
  WHEN <condition 1> THEN <value 1>
  [... WHEN <condition n> THEN <value n>]
  [ELSE <value>]
END

or

CASE
    WHEN <boolean expression 1> THEN <value 1>
    [... WHEN <boolean expression n> THEN <value n>]
    [ELSE <value>]
END

The result expressions have to be of the same type, if not a semantic error is thrown.

When using the first syntax :

  • All when expressions have to be of a type comparable to the input expression, if not a semantic error is thrown.

  • If no else condition is defined and there is no matching expression the result is null.

  • If the input expression is null and there is no else condition the result is null, even if there is a specific match on null.

For example:

SELECT
    CASE a
       WHEN 1 THEN "one"
       WHEN 2 THEN "two"
       ELSE "other"
    END
FROM [1, 2, 3] a
// result ["one", "two", "other"]

SELECT
    CASE
        WHEN a = 1 THEN "one"
        WHEN a = 2 THEN "two"
        ELSE "other"
    END
FROM [1, 2, 3] a
//result ["one", "two", "other"]

SELECT
    CASE a
       WHEN 1 THEN "one"
       WHEN 2 THEN "two"
   END
FROM [1, 2, 3] a
// result ["one", "two", null]


SELECT
   CASE
     WHEN a in ["cat", "dog"] THEN "animal"
     WHEN a in ["flower", "tree"] THEN "plant"
     ELSE "other"
   END
FROM ["dog", "tree", "rock"] a
// result ["animal", "plant", "other"]

CHOOSE

Returns the item at the specified index from a list of values.

Syntax:

CHOOSE(<int expression>, value1 [..., value n] )

Choose is an alias to a CASE expression so:

CHOOSE(index, value0,  value1, value2)

Is equivalente to:

CASE index
    WHEN 0 THEN value0
    WHEN 1 THEN value1
    WHEN 2 THEN value2
END

For example:

CHOOSE(2, "case 0", "case 1", "case 2") // "case 2"

CHOOSE(3, "case 0", "case 1", "case 2") // null

CHOOSE(1, 0, 1, 2, 3) // 1