Conditionals

Home/Conditionals

Conditionals

Conditional statements use case syntax. This is the equivalent of simple if-then-else logic and more complex conditions with multiple options.

Examples #

Simple If-then-else Operation #

[
    (table.first_name != None, func.concat(table.first_name, table.last_name),
],
else_ = table.last_name

Complex Condition with Multiple Options #

case(
    [
        (order_table.qty > 100, item_table.specialprice),
        (order_table.qty > 10, item_table.bulkprice)
    ],
    else_=item_table.regularprice
)
case(
    [
        (users_table.name == "wendy", "W"),
        (users_table.name == "jack", "J")
    ],
    else_='E'
)

The above may also be written in shorthand as:

case(
    {"wendy": "W", "jack": "J"},
    value=users_table.name,
    else_='E'
)

Coalesce #

When trying to find the first non-null value in a set of columns, the coalesce method is very helpful. This example finds the price set for the sale by looking in three possible fields:

func.coalesce(table_beta.adjusted_price, table_alpha.override_price, table_alpha.price)
* table_beta.quantity_sold

Coalesce also works for text values. This example will use the nickname if it is not null, or it will fall back to the first_name. func.coalesce(table.nickname, table.first_name).

Go to Top