-2

I am using a simple select statement and creating a column using a CONCAT function and labeling the column as Filter.

Why is the new column not being recognized?

Error message states

Invalid Column Name - Filter

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Garret Walters
  • 11
  • 4
  • What specific DMBS? Some require you to add a keyword before a calculated field or some have reserved key words. I can see either being an issue here. – Reeza Dec 08 '21 at 21:41
  • 3
    Does this answer your question? [Referring to a Column Alias in a WHERE Clause](https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause) – Stu Dec 08 '21 at 21:43
  • 2
    Your IDE (presumably SQL Server SSMS) is displaying your column alias in blue - this *means* it's a reserved keyword so you should delimit it `[Filter]` however the above explanation is probably your issue. – Stu Dec 08 '21 at 21:44

1 Answers1

0

You can't refer to column aliases in the where clause as the where is processed before the alias is materialised.

There are several workarounds, and assuming SQL Server you can do

select *
from table t
cross apply (values(concat(columna,columnb)))c([filter])
where [filter]='something'

Also note that the performance won't be great on large data sets since this criteria is non-sargable

Stu
  • 30,392
  • 6
  • 14
  • 33