SQL Server AS statement aliased column within WHERE statement

Question

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  lt  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  lt  500

User · Answer

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  lt  500

User · Answer

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  lt  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  lt  500

User · Answer

This would work on your edited question     SELECT   FROM  SELECT  lt Column List gt       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  lt  500

User · Answer

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          FROM   ON   JOIN   WHERE   GROUP BY   WITH CUBE or WITH ROLLUP   HAVING   SELECT   DISTINCT   ORDER BY   TOP      Source  http   msdn microsoft com en-us library ms189499 28v sql 110 29 aspx

User · Answer

I am not sure why you cannot use  quot lat quot  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  lt  500

User · Answer

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  lt  500    LiveDemo

User · Answer

SQL doesn t typically allow you to reference column aliases in WHERE  GROUP BY or HAVING clauses   MySQL does support referencing column aliases in the GROUP BY and HAVING  but I stress that it will cause problems when porting such queries to other databases   When in doubt  use the actual column name   SELECT t lat AS latitude    FROM poi table t  WHERE t lat  lt  500   I added a table alias to make it easier to see what is an actual column vs alias   Update    A computed column  like the one you see here   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  lt  500       doesn t change that you can not reference a column alias in the WHERE clause   For that query to work  you d have to use   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   6371 1000   acos  cos  radians 42 3936868308      cos  radians  lat       cos  radians  lon   - radians -72 5277256966      sin  radians 42 3936868308      sin  radians  lat          lt  500    Be aware that using a function on a column  IE  RADIANS lat   will render an index useless  if one exists on the column

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

Examples related to sql

Examples related to sql-server

Examples related to tsql