Links

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.
The “searched” form evaluates each boolean condition from left to right until one is true and returns the matching result.

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 '%@gmail.com' THEN 'gmail'
WHEN customer_email LIKE '%@yahoo.com' 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
NULL
NULL
gmail