Saadb - 1 year ago 109

SQL Question

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 Source

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
```