CODEWITHSUNDEEP
sqlsql-server
bendecko
bendecko

Reputation: 2783

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?

Upvotes: 1

Views: 1770

Answers (7)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

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

Upvotes: 6

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

OREDER BY is the final section of query processing. WHERE is far before that. For that reason you can not use aliases into the WHERE clause, but can use them inside the ORDER BY.

See this link for details about query processing order.

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46203

As to why you can't specify an alias in the WHERE clause, this is due to the logical order of query processing: (http://tsql.solidq.com/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf).

The WHERE clause is processed after the SELECT clause but ORDER BY is processed afterward. Column aliases can only be referenced after the SELECT clause has been processed.

Upvotes: 2

Tal Yaari
Tal Yaari

Reputation: 461

The WHERE clause is being processed before the SELECT clause and because of that you can't use any alias in the WHERE clause. The ORDER BY clause is being processed after the SELECT, so you can use aliases there.

Upvotes: 0

lssilveira
lssilveira

Reputation: 62

The ORDER BY clause is processed after the SELECT fields, in parsing. So, when it will order the clause, the alias are known but when it will filter by WHERE clause, they are not.

Try surrouding with another SELECT clause and move the WHERE and ORDER BY clauses to it.

SELECT * FROM (SELECT ...) WHERE ... ORDER BY ...

Upvotes: 0

shA.t
shA.t

Reputation: 16958

I suggest you to use an inner select like this:

SELECT *
FROM (
    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] ) t
WHERE Distance < 30 
ORDER BY Distance;

Note that ORDER BY can use aliases and column number also.

Another way is using CTE like this:

;WITH t AS (
    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] )
SELECT *
FROM t
WHERE Distance < 30
ORDER BY Distance;

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

It is the way it works. Note that ORDER BY Clause is applied after generating the entire data so the engine knows the alias name but WHERE clause does not know it until you use a derived table

select * from
(
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] 
) as t
WHERE Distance < '30' ORDER BY Distance

Upvotes: 0

Related Questions