In AWS Athena, we can use the WHEN CASE expressions to build “switch” conditions that convert matching values into another value. Such a WHEN CASE expression consists of four parts:

• CASE expression that produces the value that will be matched in the expression
• WHEN value THEN result that checks whether the expression matches the value and returns the result if both are the same
• ELSE default_result that returns the default value in the case of no matching found using the WHEN expressions. Note that if there is no ELSE default_result, the expression returns null as the default value.
• ‘END’ that finishes the CASE WHEN block

We can use multiple WHEN expressions to build long “switch” statements, like this:

1
2
3
4
5
6
7
SELECT CASE column_to_be_matched
WHEN 'value_A' THEN 'the_replacement_of_value_A'
WHEN 'value_B' THEN 'the_replacement_of_value_B'
WHEN 'value_C' THEN 'the_replacement_of_value_C'
ELSE 'default'
END
FROM some_table

We can also use the CASE WHEN expressions to retrieve values from multiple columns and choose the correct one using the CASE WHEN statement:

1
2
3
4
SELECT client_id, CASE client_type
WHEN 'private' THEN last_name
WHEN 'company' THEN company_name
END from some_clients