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
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 ...
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;
Query result
customer_email
email_domain
Last updated