Assigned query variable not recognized

Question

I have a table of items which each have latitude and longitude values to allow me to calculate distances.

I'm trying the following but it returns: Unknown column distance in where clause:

select  ( 3959 * acos( cos( radians('53.993252') ) 
* cos( radians( latitude ) ) 
* cos( radians( longitude ) 
- radians('-0.432470') ) 
+ sin( radians('53.993252') ) 
* sin( radians( latitude ) ) ) ) AS distance from items where distance < 1000

[Using column alias in WHERE clause of MySQL](http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error) — valex, Dec 23 '13 at 08:12

score 2 · Accepted Answer · answered Dec 23 '13 at 07:49

2

select  ( 3959 * acos( cos( radians('53.993252') ) 
* cos( radians( latitude ) ) 
* cos( radians( longitude ) 
- radians('-0.432470') ) 
+ sin( radians('53.993252') ) 
* sin( radians( latitude ) ) ) ) AS distance from items having distance < 1000

WHERE doesnt see aliases use HAVING.

answered Dec 23 '13 at 07:49

Mihai

26,325
7
66
81

Hmmm I was afraid of that, which leads me to the bigger question... I'll post it as a separate question - many thanks for this! – StudioTime Dec 23 '13 at 07:54

score 1 · Answer 2 · answered Dec 23 '13 at 08:05

You can use subquery -

SELECT * FROM (
  SELECT
    (3959 * ACOS(COS(RADIANS('53.993252')) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS('-0.432470')) + SIN(RADIANS('53.993252')) * SIN(RADIANS(latitude)))) AS distance
  FROM items)t
WHERE distance < 1000;

Assigned query variable not recognized

2 Answers2