0

I'm getting this error when I try to run a stored procedure.

mysql> call get_nearby;

ERROR 1054 (42S22): Unknown column 'longRadius' in 'where clause'

Here is the sql to create the stored procedure:

CREATE PROCEDURE get_nearby() BEGIN SET @lat = 10; set @long = 12;
SELECT id, 
  @long - radius / abs(cos(radians(@lat)) * 69) as longRadius, 
  (radius/69) as latRadius
FROM area
WHERE longitude between @long + longRadius and @long - longRadius
AND latitude between @lat + latRadius and @long - latRadius;
END

I have no idea why it's not recognising the stated column name. For the moment, I have set @lat, and @long at the start to simplify testing.

Any help would be greatly appreciated.

jarlh
  • 42,561
  • 8
  • 45
  • 63
frostfat
  • 155
  • 3
  • 13

1 Answers1

2

You cannot use column aliases in the where clause. You could convert that where clause to a "having" clause and then that will let you use the aliases. You can get more info here and here.

From the Mysql docs:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

You can also either restate the computation in the where clause, or use a subquery.

Community
  • 1
  • 1