Getting sql error invalid column name while using Having-CodePudding

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'

CodePudding user response：

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