# 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