mardi 4 août 2015

Cannot use Alias name in WHERE clause but can in ORDER BY

Why does this SQL not work?

The:

6371 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935')
- RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')) )

Clause just calculates the order from a search point.

Which I am aliasing (because it so longwinded) to Distance.

SELECT   [Hotel Id],latitude,longitude,establishmentname,6371 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935') - RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')) ) AS Distance  FROM [dbo].[RPT_hotels] WHERE distance < '30' ORDER BY Distance

Here I replace the "Distance < 30" with the longwinded phrase and it works fine.

I can even ORDER BY the column alias and that works!!?

SELECT   [Hotel Id],latitude,longitude,establishmentname,6371 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935') - RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')) ) AS Distance  FROM [dbo].[RPT_hotels] WHERE 6371 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935') - RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')) ) < '30' ORDER BY Distance

What am I doing wrong?

Aucun commentaire:

Enregistrer un commentaire