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 the 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 $commit_time BETWEEN run_start_time() AND run_end_time()
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 $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 3;
