0

I have this query:

Select (Country.Name + ', ' + City.Name) as Location
...
Where Location like '%Punta Cana%'

Since it works without the 'where' I naively assumed it could just work. Well, it doesn't and throws the error: Invalid column name 'Location'. Does anyone has the time to please explain why doesn't it work and what would be the working alternative?

JoseMarmolejos
  • 1,760
  • 1
  • 17
  • 34

4 Answers4

4

No, you can not refer to column aliases anywhere else in the query except ORDER BY. This is because of the way the clauses in the query are logically parsed by SQL Server - SELECT is second last. (Lots of interesting discussion about this over on dba.SE.)

Your options are to repeat the expression or use a subquery / CTE. There are already examples of subquery / CTE, so just for completeness, you could also do:

Select (Country.Name + ', ' + City.Name) as Location
...
Where (Country.Name + ', ' + City.Name) like '%Punta Cana%'

I just answered this same question yesterday, including proving that repeating the expression is not as expensive as it sounds:

Reference alias (calculated in SELECT) in WHERE clause

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
3

Use this approach instead:

; with temp as (
Select (Country.Name + ', ' + City.Name) as Location
...
)
select * from temp
Where Location like '%Punta Cana%'
Dimitri
  • 6,923
  • 4
  • 35
  • 49
1

If you want to use the alias in the ORDER BY you can wrap the entire SELECT inside of another SELECT

SELECT *
FROM 
(
    Select (Country.Name + ', ' + City.Name) as Location
    ...
) x
Where x.Location like '%Punta Cana%'

Otherwise you will have to use the expression again in the ORDER BY clause

Taryn
  • 242,637
  • 56
  • 362
  • 405
0

Location is not the column name. Its just an Alias that you are creating.

Asdfg
  • 11,362
  • 24
  • 98
  • 175