Page 1 of 1

SELECT AS error

Posted: Fri Dec 17, 2004 4:52 pm
by danielw1
The following query:

Code: Select all

SELECT id, name, state, latitude, longitude, url, acos(sin(39.2346)*sin(tblcampgrounds.latitude)+cos(39.2346)*cos(tblcampgrounds.latitude)*cos(123.203-tblcampgrounds.longitude)) AS distance FROM tblcampgrounds WHERE distance <= '30' AND state = 'CA' ORDER BY distance ASC
Errors. THe purpose of that long equation within the acos() function is to determine the surface distance on a globe between two points (tblcampgrounds holds the coordinates of campgrounds, if you can't tell already). MySQL errors out, and says that the column 'distance' is not valid in the where clause... but I can't seem to figure out why. When I just select the acos() as distance, no FROM, WHERE, ORDER or other columns, it works fine.

Any suggestions?

Posted: Fri Dec 17, 2004 5:59 pm
by andre_c
From the MySQL Documentation:

Code: Select all

It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed.
http://dev.mysql.com/doc/mysql/en/SELECT.html