Highlighted

Can you apply a WHERE clause to a computed column

Contributor ,
Aug 22, 2020

Copy link to clipboard

Copied

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

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;

TOPICS
Advanced techniques, Reporting

Views

41

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Can you apply a WHERE clause to a computed column

Contributor ,
Aug 22, 2020

Copy link to clipboard

Copied

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

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;

TOPICS
Advanced techniques, Reporting

Views

42

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Aug 22, 2020 0
Contributor ,
Aug 22, 2020

Copy link to clipboard

Copied

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)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 22, 2020 0
Participant ,
Aug 23, 2020

Copy link to clipboard

Copied

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;

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 23, 2020 1
Contributor ,
Aug 24, 2020

Copy link to clipboard

Copied

Indeed, that's more elegant. I know enough about SQL to get by most days, but not enough to always use it well. Thanks! 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 24, 2020 0