General Usage Conditionals

Simple If-then-else Operation, Complex Condition with Multiple Options, Coalesce

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

case(
        (table.first_name.isnot(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)
Last modified June 21, 2022 at 10:56 AM EST: Updated Case Statements (7d7abf6)