0

This is a simple example of the problem:

SELECT CASE WHEN a = 5 THEN 0 ELSE 1 END AS x
FROM table
WHERE x = 0;

I will get an error message that x is not amongst the possible column names. Could anyone advise me how to solve this?

Stefan Wuebbe
  • 2,109
  • 5
  • 17
  • 28
Tamás Godányi
  • 69
  • 5
  • in some DB like MySQL, this works but in hive this doesnt. You can use CTE or use the select in a inner query and use it in outer qry. https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause – Koushik Roy Jul 15 '22 at 09:35

1 Answers1

0
SELECT 
   CASE WHEN a = 5 THEN 0 
   ELSE 1 
END AS x FROM table 
WHERE 
 CASE WHEN a = 5 THEN 0 
   ELSE 1 
END=0
Sergey
  • 4,719
  • 1
  • 6
  • 11