How can I reference a column alias in another place in the same select statement?

I want to do this (hypothetical example):

SELECT
  (temp_c * 9 / 5 + 32) AS temp_f,
  CASE
    WHEN temp_f < @VALUE1 THEN "cold"  -- ERROR HERE
    WHEN temp_f > @VALUE2 THEN "hot"   -- ERROR HERE
    ELSE "warm"
  END AS temp_description,
  other_columns
FROM some_table WHEN some_condition
ORDER BY temp_f;  -- NO ERROR HERE

But apparently I cannot refer to the name temp_f in the temp_description expression.

I know I can do something like this:

SELECT
  temp_f,
  CASE
    WHEN temp_f < @VALUE1 THEN "cold"  -- NO ERROR HERE
    WHEN temp_f > @VALUE2 THEN "hot"   -- NO ERROR HERE
    ELSE "warm"
  END AS temp_description,
  other_columns
FROM (
  SELECT (temp_c * 9 / 5 + 32) AS temp_f, *
    FROM some_table WHEN some_condition
)
ORDER BY temp_f;  -- NO ERROR HERE

But that’s much clunkier than I want, especially in the context of my real-life use case that, for permissions reasons, prevents me from using * to select all table columns in the subquery, so I’d have to repeat all the columns I am using multiple times (plus I don’t want it to impact performance, not sure whether it would). Is there a better way?

Answer

The behavior you are seeing is by design. In the SQL Standard, and Most RDBMS’s, you cannot reference a field alias in another field expression of the same select statement, or even in the where clause. This has to do with the order that the statements are parsed. You can reference a field alias in GROUP BY and ORDER BY because those statements are parsed later, and you can reference the field alias of a subquery in the outer query. The typical way to handle this is to repeat the calculation. It doesn’t look very clean, but it is no less efficient. Your query would become:

SELECT
  (temp_c * 9 / 5 + 32) AS temp_f,
  CASE
    WHEN (temp_c * 9 / 5 + 32) < @VALUE1 THEN "cold"
    WHEN (temp_c * 9 / 5 + 32) > @VALUE2 THEN "hot"
    ELSE "warm"
  END AS temp_description,
  other_columns
FROM some_table WHEN some_condition
ORDER BY temp_f;

If you were feeling ambitious, have the necessary privileges and your RDBMS supports it, you could create a User Defined Function to do the conversion, but you would still have to call the function in all three places.