How to use WHEN CASE queires in AWS Athena
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 expressionWHEN value THEN result
that checks whether theexpression
matches thevalue
and returns theresult
if both are the sameELSE default_result
that 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 returnsnull
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
You may also like
Remember to share on social media! If you like this text, please share it on Facebook/Twitter/LinkedIn/Reddit or other social media.
If you want to contact me, send me a message on LinkedIn or Twitter.
Would you like to have a call and talk? Please schedule a meeting using this link.