Getting sql error invalid column name while using Having

Question

I am trying to execute this query in SSMS in SQL but getting error like as below :

SQL query :

SELECT 
    LocationId, 
    (3959 *
     acos(cos(radians(37)) * 
     cos(radians(Latitude)) * 
     cos(radians(Longitude) - 
     radians(-122)) + 
     sin(radians(37)) * 
     sin(radians(Latitude)))) AS distance 
FROM 
    [dbo].[UserLocation]
HAVING 
    distance < 28 
ORDER BY 
    distance

I get this error:

Msg 207, Level 16, State 1, Line 13
Invalid column name 'distance'

You can't reference a column by it's alias in the `HAVING` (or the `WHERE`, where that clause *should* be) that was define in the same scope's `SELECT`. You either have to repeat the expression or use a CTE/Subquery. — Thom A, Dec 06 '21 at 15:14
To expand about my comment on the `WHERE`, the `HAVING` clause is meant to be used against aggregate functions, as these cannot be accessed in the `WHERE`. For example, something like `HAVING COUNT(MyColumn) > 1`. For a clause like yours, that should be in the `WHERE`. This is especially true considering your query has no aggregation or even a `GROUP BY`. — Thom A, Dec 06 '21 at 15:17
You can refer to [this](https://stackoverflow.com/a/4596739/685467) post for comprehensive explanation of your issue. — Hamlet Hakobyan, Dec 06 '21 at 15:18
Putting clauses that should be in the `WHERE` in the `HAVING` *could* detrimental to the performance of the query, as such clauses are *normally* processed after the `GROUP BY` (though for simple queries the data engine is *normally* "smart" enough to move such clauses to the `WHERE` for processing). — Thom A, Dec 06 '21 at 15:19
You could materialize your column using an *apply()* for filtering in the *where* clause — Stu, Dec 06 '21 at 15:20
@Stu - I was about to write that as an answer (along with materialising through a CTE), you should write the answer and get the votes :) — MatBailie, Dec 06 '21 at 15:21

score 3 · Accepted Answer · answered Dec 06 '21 at 15:29

A few ways you can approach this. Hopefully the following is syntactically correct as obviously unable to directly test.

You can use a derived query or CTE such as

with d as (
 LocationId, 
 (
   3959 *
   acos(cos(radians(37)) * 
   cos(radians(Latitude)) * 
   cos(radians(Longitude) - 
   radians(-122)) + 
   sin(radians(37)) * 
   sin(radians(Latitude)))
 ) AS distance 
 FROM dbo.UserLocation
)
select *
from d
where distance < 28
order by distance

You could also use an apply

select LocationId, distance
from dbo.UserLocation
cross apply(values(
   3959 *
   Acos(Cos(Radians(37)) * 
   Cos(Radians(Latitude)) * 
   Cos(Radians(Longitude) - 
   Radians(-122)) + 
   Sin(Radians(37)) * 
   Sin(Radians(Latitude)))
    ))v(distance)
where distance < 28
order by distance

Getting sql error invalid column name while using Having

1 Answers1