How to fix SQL for AWS Athena

Question

I'm moving SQL from Redshift to AWS Athena, but I'm having trouble because the SQL is giving me errors.

SELECT abc_name AS bca_name,
CASE
  WHEN name = 'aaa' THEN 'AAC'
  WHEN name = 'bbb' THEN 'BBA'
  WHEN name = 'ccc' THEN 'CCB'
END AS abc
FROM master_table
WHERE abc IS NOT NULL;

This SQL gave me an error: it says that there is no abc column because abc in the where clause is read earlier than in the select clause.

How can I get this to work?

Answer 1

This is a common SQL problem. You can't refer to an alias defined in the SELECT clause in the WHERE clause of the same query. But you don't even need to use the alias to get your desired logic, just use a WHERE IN (...):

SELECT abc_name AS bca_name,
       CASE name WHEN 'aaa' THEN 'AAC'
                 WHEN 'bbb' THEN 'BBA'
                 WHEN 'ccc' THEN 'CCB' END AS abc
FROM master_table
WHERE name IN ('aaa', 'bbb', 'ccc');

If you really did need to refer to the alias, you would have to either use a subquery or just repeat the CASE expression. For the former option:

SELECT bca_name, abc
FROM
(
    SELECT abc_name AS bca_name,
           CASE name WHEN 'aaa' THEN 'AAC'
                     WHEN 'bbb' THEN 'BBA'
                     WHEN 'ccc' THEN 'CCB' END AS abc
    FROM master_table
) t
WHERE abc IS NOT NULL;