CASE

The standard SQL CASE expression has two forms.

The “simple” form searches each value expression from left to right until it finds one that equals the given expression and then returns the matching result.

See: CASE ... WHEN

The “searched” form evaluates each boolean condition from left to right until one is true and returns the matching result.

See: CASE WHEN ...

CASE ... WHEN

Syntax

CASE <expression>
    WHEN <value> THEN <result>
    [ WHEN ... ]
    [ ELSE <result> ]
END

Returns

The result for the first value found that matches expression is returned.

If no match is found, the result from the ELSE clause is returned if it exists; otherwise null is returned.

Examples

SQL

CREATE JOB function_operator_example
    ADD_MISSING_COLUMNS = true	
    AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
    SELECT 
        ordertype,
        (CASE LOWER(ordertype)
            WHEN 'shipping' THEN true
            WHEN 'pickup' THEN false
            ELSE false
        END) AS is_shipped
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE time_filter()
    LIMIT 3;

Query result

ordertype	is_shipped
SHIPPING	true
shipping	true
pickup	false

CASE WHEN ...

Syntax

CASE
    WHEN <condition> THEN <result>
    [ WHEN ... ]
    [ ELSE <result> ]
END

Returns

The result corresponding to the first true condition is returned.

If no conditions are true, the result from the ELSE clause is returned if it exists; otherwise null is returned.

Examples

SQL

CREATE JOB function_operator_example
    ADD_MISSING_COLUMNS = true	
    AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
    SELECT 
        customer_email,
        (CASE
            WHEN customer_email LIKE '[e-mail]' THEN 'gmail'
            WHEN customer_email LIKE '[e-mail]' THEN 'yahoo'
        END) AS email_domain
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE time_filter()
    LIMIT 3;

Query result

customer_email	email_domain
[e-mail]	NULL
[e-mail]	NULL
[e-mail]	gmail