Can you apply a WHERE clause to a computed column

Question

I have a MySQL query that returns locations based on a computed column. I can apply a limit clause to the results, but I can't figure out how to apply a WHERE clause. The computed column is an integer, and I'd like the results to be less than a given number from the distance column, as in:

SELECT
MID,
AptName,
Address,
City,
State,
Zip,
ROUND((ACOS((SIN(#latitude#/57.29577951) * SIN(lat/57.29577951)) +
(COS(#latitude#/57.29577951) * COS(lat/57.29577951) *
COS(lon/57.29577951 - #longitude#/57.29577951)))) * 3963,2)
AS distance
FROM
tbl_maps
WHERE distance < 5.00 (the query runs fine without this line)
ORDER BY
distance ASC;

Any help appreciated.

John

John123 · Accepted Answer

You can use a sub query to avoid defining the calculation multiple times.

SELECT
MID,
AptName,
Address,
City,
State,
Zip,

distance

from (

SELECT
MID,
AptName,
Address,
City,
State,
Zip,
ROUND((ACOS((SIN(#latitude#/57.29577951) * SIN(lat/57.29577951)) +
(COS(#latitude#/57.29577951) * COS(lat/57.29577951) *
COS(lon/57.29577951 - #longitude#/57.29577951)))) * 3963,2)
AS distance
FROM
tbl_maps

) as x
WHERE distance < 5.00
ORDER BY
distance ASC;

John_Allred · Answer

I jiggered with it and arrived at a solution. Perhaps someone can describe one more elegant. I cast the WHERE clause as the calculation:
WHERE
ROUND((ACOS((SIN(#latitude#/57.29577951) * SIN(lat/57.29577951)) +
(COS(#latitude#/57.29577951) * COS(lat/57.29577951) *
COS(lon/57.29577951 - #longitude#/57.29577951)))) * 3963,2)
<= (the desired distance)

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded