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 expressionthat produces the value that will be matched in the expression -
WHEN value THEN resultthat checks whether theexpressionmatches thevalueand returns theresultif both are the same -
ELSE default_resultthat returns the default value in the case of no matching found using the WHEN expressions. Note that if there is noELSE default_result, the expression returnsnullas the default value. - ‘END’ that finishes the
CASE WHENblock
We can use multiple WHEN expressions to build long “switch” statements, like this:
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:
SELECT client_id, CASE client_type
WHEN 'private' THEN last_name
WHEN 'company' THEN company_name
END from some_clients
Want to build AI systems that actually work?
Download my expert-crafted GenAI Transformation Guide for Data Teams and discover how to properly measure AI performance, set up guardrails, and continuously improve your AI solutions like the pros.