SELECT AS error

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
danielw1
Forum Newbie
Posts: 6
Joined: Sun Jan 05, 2003 12:54 pm
Location: Kansas
Contact:

SELECT AS error

Post 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?
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post 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
Post Reply