Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- >
- ColdFusion
- >
- Can you apply a WHERE clause to a computed column

Highlighted

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

Participant

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

Community Guidelines

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

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

Participant

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

Community Guidelines

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

John_Allred

Aug 22, 2020
0
/t5/coldfusion/can-you-apply-a-where-clause-to-a-computed-column/td-p/11379848
3
Replies
3

Contributor
,

Aug 22, 2020

Copy link to clipboard

Copied

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)

Community Guidelines

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

Reply

Loading...

John_Allred

Aug 22, 2020
0
/t5/coldfusion/can-you-apply-a-where-clause-to-a-computed-column/m-p/11379887#M186314
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;

Community Guidelines

Reply

Loading...

John123

Aug 23, 2020
1
/t5/coldfusion/can-you-apply-a-where-clause-to-a-computed-column/m-p/11381088#M186316
John_Allred
LATEST

Contributor
,

Aug 24, 2020

Copy link to clipboard

Copied

Community Guidelines

Reply

Loading...

John_Allred

Aug 24, 2020
0
/t5/coldfusion/can-you-apply-a-where-clause-to-a-computed-column/m-p/11382828#M186318
Using the Community
Experience League
Terms of Use
Privacy Policy
Cookie preferences
AdChoices
Language:

- Deutsch
- English
- Español
- Français
- 日本語コミュニティ
- Português

Copyright © 2020 Adobe. All rights reserved.