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

Question

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?

Answer 1

This happens because of natural query processing order, which is the following:

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

You're assigning your alias in SELECT statement. As you can see WHERE is processed before SELECT and ORDER BY comes after it. That's the reason. Now what are the workarounds:

• Subqueries. But they can be hard to read.
• CROSS APPLY. This should beautify your code a bit and is recommended method.

CROSS APPLY will assign alias before WHERE statement, making it usable in it.

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

If you want to find out more. Please read this question: What is the order of execution for this SQL statement