Saadb Saadb - 2 months ago 23
SQL Question

SQL query of Haversine formula in SQL server

I am trying to get drivers who visited certain area from SQL server 2014 database. The table is named DriverLocationHistory.

Here is the sql query I used :

SELECT id, ( 6371 * acos( cos( radians(37) ) * cos( radians( latitude ) )
* cos( radians( Longitude ) - radians(-122) ) + sin( radians(37) ) * sin(radians(latitude)) ) ) AS distance
FROM DriverLocationHistory
HAVING distance < 5
ORDER BY distance


When I execute the query I get this error :

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

Answer

You can't use an alias in a Where clause. This is because the where clause is processed before, or as, the result set is being generated, and the alias is not assigned until the result set has been generated. Only in an aggregate query (where there is a group By) can you do this, because then the alias is assigned to the value of some expression before the aggregation is processed. Your query must use the full expression in both the where clause (does not need to be a Having clause) and in the order by:

SELECT id, 
  ( 6371 * acos( cos( radians(37) )  
      * cos( radians( latitude ) ) 
      * cos( radians( Longitude ) - radians(-122) ) + sin( radians(37) ) 
      * sin(radians(latitude)) ) ) AS distance 
FROM DriverLocationHistory 
Where 6371 * acos( cos( radians(37) )  
      * cos( radians( latitude ) ) 
      * cos( radians( Longitude ) - radians(-122) ) + sin( radians(37) ) 
      * sin(radians(latitude)) ) < 5 
ORDER BY 6371 * acos( cos( radians(37) )  
      * cos( radians( latitude ) ) 
      * cos( radians( Longitude ) - radians(-122) ) + sin( radians(37) ) 
      * sin(radians(latitude)) )

as mentioned in comments, you can use the alias in the order by,

Or, you could also perform the computation and alias assignment in a subquery:

SELECT id, distance
From (Select ( 6371 * acos( cos( radians(37) )  
          * cos( radians( latitude ) ) 
          * cos( radians( Longitude ) - radians(-122) ) + 
             sin( radians(37) ) 
          * sin(radians(latitude)) ) ) distance 
      From DriverLocationHistory)z
Where distance < 5 
ORDER BY distance
Comments