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

Question

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?

Answer 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

Answer 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.

Answer 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

Answer 4

I think you can only use AS to display the final values. To do a comparision, the select should be returned from another select statement inside it.

Like:

SELECT a.disfromaddr 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) 
a WHERE a.disfromaddr < 2

You can try this.

Answer 5

select 
    t1.*,
    t2.brand_name,
    t3.category_name,
    if(
        t3.parent_category_id=0,
        t3.cat_key,
        (
            select 
              concat(t3b.cat_key,'/',t3.cat_key) 
            from master_category t3b 
            where t3b.category_id=t3.parent_category_id 
        )
    ) as cat_key,
    (
        select min(t4.product_MSP) 
        from trans_products t4  
        where t1.product_id=t4.product_id 
    ) as product_MSP,
    (
        select min(t4.product_MRP) 
        from trans_products t4 
        where t1.product_id=t4.product_id 
    ) as product_MRP 
from master_products t1,
master_brand t2,
master_category t3 
where t1.status=1 
and t2.status=1 
and t2.brand_id=t1.brand_id 
and t3.category_id=t1.category_id 
and t3.display_status=1 
and t1.category_id=6 
and product_MSP between '1000' and '500000'