Ternary Conditional Operator (?:)

condition ? trueClause : falseClause

condition is a BOOL scalar, vector, or a variable/expression/function call that produces a BOOL scalar or vector. Note:

• condition cannot be null.
• If it's an expression, it must be enclosed in parentheses. Otherwise, parentheses are optional.

trueClause and falseClause are variables, function calls, or expressions. Parentheses are not required.

The ternary conditional operator (?:) evaluates condition and executes either trueClause or falseClause based on the result.

• If condition is a boolean scalar:
  • If true, only trueClause is executed and returned.
  • If false, only falseClause is executed and returned.
• If condition is a boolean vector, it's treated as iif(condition, trueClause, falseClause), which means both trueClause and falseClause may be executed.

Note:

• trueClause and falseClause can be nested ternary expressions.
• This operator can be used in both SQL and non-SQL scripts.
• Variables and columns can be used within the expressions.

Let's start with some simple usage examples:

true ? 1 : 0
//output: 1

true true true false false false ? 1..6 : 6..1
//output: [1,2,3,3,2,1]

(1..6==4) ? 1 : 0
//output: [0,0,0,1,0,0]

a = 1..6
a1 = a>3
a1 ? 1 : 0
//output: [0,0,0,1,1,1]

b = 1 2 3
(b<=2) ? 4*b : 5*b-1
//output: [4,8,14]

true ? add(1,1) : and(1,4)
//output: 2

Here's an example from the industrial IoT sector. Users often need to monitor various sensor data and trigger alerts or adjust device operations based on preset thresholds. In this example, we get a device status (deviceStatus) and temperature value (temperature) from a temperature sensor. Using a ternary expression, we can quickly determine if the device is online and if the temperature exceeds the threshold.

deviceStatus = "online"
temperature = 55 
(deviceStatus == "online" && temperature <= 45) ? "pass" : "warning"
//output: warning

The trueClause and falseClause can be of any data type and of any form. Here's a simple example:

x = 1 2 3 4;
y = 2.3 4.6 5.3 6.4;
p = dict(x, y);

q = 1:3

((3 add 2) <= 6) ? p : q

/*output:
key value
1   2.3
2   4.6
3   5.3
4   6.4
*/

The following example applies the ternary operator to tables. First, we generate two tables:

t = table(1..5 as id, 11..15 as x);
t1 = table(take(12,5) as a, take(14,5) as b);
t;

t1;

Here, we use a nested ternary expression in a SQL statement to update the data in table t:

update t set x = ((x < t1.a) ? t1.a : (x > t1.b) ? t1.b : x);
t;

Related function: iif