Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

sql - 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...