[sql] SQL Server AS statement aliased column within WHERE statement

I want to execute a query in which I rename one of the columns using the 'AS' statement and reuse that aliased column name within the 'WHERE' statement. Below is an example:

SELECT lat AS latitude 
FROM poi_table 
WHERE latitude < 500

The problem here is that SQL Server does not like this query because of the WHERE clause and the AS statement name being referenced in the WHERE clause. Can anyone explain why this is happening and what I can do to remedy my situation?

Suppose I were to have a formula that I have aliased in the SELECT portion of the query, how do I tackle that?

SELECT *, 
( 6371*1000 * acos( cos( radians(42.3936868308) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-72.5277256966) ) + sin( radians(42.3936868308) ) * sin( radians( lat ) ) ) ) 
AS distance
FROM poi_table 
WHERE distance < 500;

SQL Server is tuned to apply the filters before it applies aliases (because that usually produces faster results). You could do a nested select statement. Example:

SELECT Latitude FROM 
(
    SELECT Lat AS Latitude FROM poi_table
) A
WHERE Latitude < 500

I realize this may not be what you are looking for, because it makes your queries much more wordy. A more succinct approach would be to make a view that wraps your underlying table:

CREATE VIEW vPoi_Table AS 
SELECT Lat AS Latitude FROM poi_table

Then you could say:

SELECT Latitude FROM vPoi_Table WHERE Latitude < 500

Both accepted answer and Logical Processing Order explain why you could not do what you proposed.

Possible solution:

• use derived table (cte/subquery)
• use expression in WHERE
• create view/computed column

From SQL Server 2008 you could use APPLY operator combined with Table valued Constructor:

SELECT *, s.distance
FROM poi_table 
CROSS APPLY (VALUES(6371*1000*acos(cos(radians(42.3936868308))*cos(radians(lat))*cos(radians(lon)-radians(-72.5277256966))+sin(radians(42.3936868308))*sin(radians(lat))))) AS s(distance)
WHERE distance < 500;

LiveDemo

I am not sure why you cannot use "lat" but, if you must you can rename the columns in a derived table.

select latitude from (SELECT lat AS latitude FROM poi_table) p where latitude < 500

I am not sure why you cannot use "lat" but, if you must you can rename the columns in a derived table.

select a.latitude from (SELECT lat AS latitude FROM poi_table) a where latitude < 500

This would work on your edited question !

SELECT * FROM (SELECT <Column_List>,  
( 6371*1000 * acos( cos( radians(42.3936868308) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-72.5277256966) ) + sin( radians(42.3936868308) ) * sin( radians( lat ) ) ) )  
AS distance 
FROM poi_table) TMP
WHERE distance < 500; 

Logical Processing Order of the SELECT statement

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

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

Source: http://msdn.microsoft.com/en-us/library/ms189499%28v=sql.110%29.aspx