Search code examples
sqlsql-serverwhere-clauselateral-join

Using a where clause on a newly created field


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