Using a where clause on a newly created field

Question

How do i perform a where action on a newly created field that is populated with the values of two fields?

select upper(column_a + ' ' + column_b) as newly_created_field,
some_other_field
from table_xyz
where newly_created_field = 'NEW VALUE'

Answer 1

You cannot refer to the alias in the WHERE clause at the same level as the SELECT in which it was defined. Your options include repeating the entire expression in the WHERE clause:

SELECT UPPER(column_a + ' ' + column_b) AS newly_created_field,
       some_other_field
FROM table_xyz
WHERE UPPER(column_a + ' ' + column_b) = 'NEW VALUE'

Or, you may subquery and then refer to the alias directly:

SELECT newly_created_field, some_other_field
FROM
(
    SELECT UPPER(column_a + ' ' + column_b) AS newly_created_field,
       some_other_field
    FROM table_xyz
) t
WHERE newly_created_field = 'NEW VALUE';

Answer 2

In SQL Server, I find that cross apply and values provide an elegant solution to the question.

You can compute the value once in the from clause, and then use it everywhere in the query (including the where clause):

select x.newly_created_field, t.some_other_field
from table_xyz t
cross apply ( values (upper(t.column_a + ' ' + t.column_b) ) x(newly_created_field)
where x.newly_created_field = 'NEW VALUE'