23

I'm using an alias to refer to a computed column. Here is a snippet from the actual code I'm trying to make work, to compute similarity and return matches where the similarity score is 3 or higher.

select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]
where similarity > 2
order by similarity desc

Exception Message:

Invalid column name 'similarity'.

As similarity is not a real column, how would I make this work?

Erik Philips
  • 53,428
  • 11
  • 128
  • 150
Marcus K
  • 779
  • 1
  • 10
  • 22
  • possible duplicate of ["Invalid column name" error on SQL statement from OpenQuery results](http://stackoverflow.com/questions/46354/invalid-column-name-error-on-sql-statement-from-openquery-results) – Brock Adams Jul 06 '11 at 03:17

4 Answers4

39

Column aliases and computations are performed in the projection (SELECT) phase of the query, which occurs after the selection (WHERE and JOIN) phase. Because of this, they can't be referenced in the WHERE clause or in a JOIN condition because they do not yet exist. You can either use your query with the SELECT clause as a subquery or you can duplicate the computation in the WHERE clause:

select * 

from
(select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]) src

where similarity > 2
order by similarity desc

or

select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]
where difference([FirstName], 'mitch') > 2
order by similarity desc
Adam Robinson
  • 182,639
  • 35
  • 285
  • 343
11

All answers can solve your problem but for complicated situation you just can't duplicate your query.

The correct way is by using CROSS and APPLY

select [FirstName], similarity
from [Dev].[dbo].[Name]
cross apply
  (
     select similarity =
     difference([FirstName], 'mitch')  
  )computed_column
where similarity > 2
order by similarity desc

whit CROSS and APPLY you can use your computed column everywhere on the query

ktutnik
  • 6,882
  • 1
  • 29
  • 34
  • 1
    I think this should be the answer. Note that you are required to give the cross apply a name. In this case, even though the "computed_column" alias is not used, you still have to define/include it. Keep it DRY! –  Apr 06 '15 at 14:10
7

Try:

SELECT * 
  FROM (
        SELECT [FirstName], difference([FirstName], 'mitch') as similarity
            FROM [Dev].[dbo].[Name]
        ) a
WHERE similarity > 2
ORDER BY similarity desc
Chandu
  • 81,493
  • 19
  • 133
  • 134
1
select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]
where difference([FirstName], 'mitch') > 2
order by 2 desc

You can't reference column aliases in where clause

Bohemian
  • 412,405
  • 93
  • 575
  • 722