Menu
When I tried to perform the below code in SQL Server 2005 I am getting the error
Invalid column name DistanceFromAddress
Code:
select SQRT(POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) + POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)) * 62.1371192 AS DistanceFromAddress from tblProgram where DistanceFromAddress < 2 I am getting the values correctly using the select statement,but when i tried to check the condition where DistanceFromAddress < 2 I am getting the error.
How can I solve this issue?
361
In PROC SQL, a column alias can be used in a WHERE clause, ON clause, GROUP BY clause, HAVING clause, or ORDER BY clause. In the ANSI SQL standard and ISO SQL standard, the value that is associated with a column alias does not need to be available until the ORDER BY clause is executed.
If the alias_name contains spaces, you must enclose the alias_name in quotes. It is acceptable to use spaces when you are aliasing a column name. However, it is not generally good practice to use spaces when you are aliasing a table name. The alias_name is only valid within the scope of the SQL statement.
Yes, you can certainly use column aliases in your "order by" clause.
You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses. Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.
You can't use aliased columns in a WHERE clause. You can try using a derived table. Perhaps something like this (sorry, not tested):
SELECT * FROM (SELECT SQRT(POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) + POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)) * 62.1371192 AS DistanceFromAddress from tblProgram) mytable WHERE DistanceFromAddress < 2 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
