Using a where clause on a newly created field

December 30, 2022

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'

>Solution :

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';