How to use alias column name in where clause in SQL Server

When I tried to perform the below code in SQL Server 2005 I am getting the error

Invalid column name DistanceFromAddress

Code:

select 
    SQRT(POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +   
    POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)) * 62.1371192 AS DistanceFromAddress 
from 
    tblProgram 
where 
    DistanceFromAddress < 2

I am getting the values correctly using the select statement,but when i tried to check the condition where DistanceFromAddress < 2 I am getting the error.

How can I solve this issue?

Solution 1:

You can't use aliased columns in a WHERE clause. You can try using a derived table. Perhaps something like this (sorry, not tested):

SELECT * FROM
(SELECT SQRT(POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +   
 POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)) * 62.1371192 
 AS DistanceFromAddress from tblProgram) mytable
WHERE DistanceFromAddress < 2

Solution 2:

The WHERE clause is processed before the SELECT clause(*), and so the aliases aren't available. Move to using a subquery or CTE - here's a CTE:

; with Distances as (
    select SQRT(POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +   
 POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)) * 62.1371192 
 AS DistanceFromAddress
    from tblProgram
)
select * from Distances where DistanceFromAddress < 2

(*) - well, systems are free to re-order operations as they see fit, so long as the result is "as if" the SQL statement was processed in a certain logical order. Of course, where this all goes wrong with SQL Server is where it produces errors because of conversion issues in the SELECT clause for rows/values that should be eliminated by the WHERE clause.

Solution 3:

select 
  SQRT(
    POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +
    POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)
  ) * 62.1371192 AS DistanceFromAddress 
from tblProgram 
having DistanceFromAddress < 2

could work (although I think not, without having a group by clause as well).

The problem is that you can only use names in the scope of the table(s) you select inside the where clause. Where is a pre filter that filter out rows before they are selected, so expressions like this in the field definition are not executed yet and the aliases are therefor not available.

The Having clause works as a post filter after grouping and can use aliases from the query, although I'm afraid you will need to have an actual group by clause (not sure).

The alternative is to have a sub-select (derived table or select in select), where you first select the distances for each row, and then select only the relevant distances from those results. This will work:

select d.DistanceFromAddress
from
  (select 
    SQRT(
      POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +
      POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)
    ) * 62.1371192 AS DistanceFromAddress 
  from tblProgram) d
where d.DistanceFromAddress < 2

Or you can repeat the expression. This makes your query harder to maintain, but in some cases this might work for you. For instance if you won't want to return the actual distance, but only, say, the name of the point of interest at that distance. In that case, you need to have the expression only in the where clause, in which case the maintainability argument is gone, and this solution is a perfect alternative.

select 
  tblProgram.POIname
  /* Only if you need to return the actual value
  , SQRT(
    POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +
    POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)
  ) * 62.1371192 AS DistanceFromAddress */
from tblProgram
where 
  -- Use this if you only want to filter by the value.
  SQRT(
    POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +
    POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)
  ) * 62.1371192 < 2