[SOLVED] Using select ... as, and then using it in the WHERE

[scotchegg78]

HI Guys

 

I have hte following...

SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(52.478)) * SIN(RADIANS(job_postcodes.Lat)) + COS(RADIANS(52.478))* COS(RADIANS(job_postcodes.Lat)) * COS(RADIANS((-1.741)-job_postcodes.Long)))) as distance, PostCode FROM job_postcodes WHERE distance < 30

 

however I get the error distance unknown column??

Order by distance works, but not the where??

 

thanks for any info or help

[PFMaBiSmAd]

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

 

To do what you want, you need to use a HAVING clause -

 

...HAVING distance < 30

[scotchegg78]

thanks for the reply, i will try it, but i have it working now using this...

 

SELECT sub.distance,jp.PostCode FROM (SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(52.478)) * SIN(RADIANS(jp.Lat)) + COS(RADIANS(52.478))* COS(RADIANS(jp.Lat)) * COS(RADIANS((-1.741)-jp.Long))))as distance FROM job_postcodes jp) sub, 
job_postcodes jp WHERE sub.distance < 20

 

however it takes 1.9 seconds , which i think is a bit long.

 

[scotchegg78]

yeah having will do it lol

 

works in 0.03 secs