Conditional operations

Ternary If Statements

Conditional operations allow users to check for certain conditions and then assign different values depending on the outcome. This is commonly implemented as if-then-else statements in programming. Another common form, but with fewer characters/keystrokes, is the inline if, or ternary if.

The ternary operator is a conditional expression that includes three operands:

  • condition that evaluates to true or false
  • outcome 1 (if true)
  • outcome 2 (if false)

The syntax for a ternary if statement with only one condition to evaluate follows: condition ? [value if condition is true] : [value if condition is false]

The question mark (?) separates the condition from the values, and the colon (:) is used to separate the values to be used after evaluating the condition.

The expression x ? y : z evaluates as follows:

  • If x is true, the operation evaluates to y.
  • If x is false, the operation evaluates to z.

Example

This query creates two tables that simply tell us whether or not the value in the Size column is a round number, or if the value in the Last column is null:

t = db.t("LearnDeephaven", "StockTrades").where("Date = `2017-08-21`")

t2 = t.update("Round = Size % 100 == 0 ? `yes` : `no`")

t3 = t.update("Null = isNull(Last) ? `yes` : `no`")

Nested Ternary If Statements

Ternary if statements can also be nested to evaluate more than one condition. Here is the syntax when evaluating two conditions:

condition1 ? (condition2 ? value1 : value2) : value3

The expression x ? (y ? 1 : 2) : 3 evaluates as follows:

  • If both x and y are true, the expression evaluates to 1.
  • If x is true, and y is false, the expression evaluates to 2.
  • If x is false, the expression evaluates to 3.

Example

t4 = t.update("Buy = Size<50 ? (Last>100 ? `n` : `y`) : `n`")

This query creates a new column "Buy" that will contain a string value of "y" or n".

  • If the row's Size value is less than 50, the query then evaluates the row's Last value:
    • If the Last value is greater than 100, the Buy column will contain "n".
    • If the Last value is less than 100, the Buy column will contain "y".
  • If the row's Size value is greater than 50, the query does not evaluate the Last value, and the Buy column will contain "n".