Hello there, MS SQL doesn't mind the HAVING clause, it just doesn't like using anything it renames through an AS clause, which is odd to me.
Yes, repeating the calculation in the WHERE clause would work, but I'd also need to repeat it in the ORDER BY clause. Considering the calculation to be heavy, I find this to be excessive, and really inefficient. I might try doing this using a stored procedure. I think that might be doable... I wonder if MS SQL is smart enough to recognize that the second and third calculations are repeats, and actually only do it once? Thanks, Jacques On Nov 10, 5:12 pm, Andrew Leach <[email protected]> wrote: > On Nov 10, 9:38 pm,quesnelj<[email protected]> wrote: > > > > > The MySQL statement: > > > SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * > > cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin > > ( radians( lat ) ) ) ) AS distance > > FROM markers > > HAVING distance < 25 > > ORDER BY distance > > > Does not work with MS SQL Server as "distance" cannot be used in the > > HAVING and ORDER BY clauses. > > > Does any one have a suggestion? > > As you say, different SQL dialects accept different things. You could > try replacing the keyword HAVING with WHERE -- MySQL can't accept > aliases in the WHERE clause but will allow HAVING; SQL Server doesn't > like HAVING but might allow WHERE with an alias. Otherwise you need to > put your calculation in the WHERE clause, which is far less efficient > but certain to work: > > SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * > cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin > ( radians( lat ) ) ) ) AS distance > FROM markers > WHERE ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * > cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin > ( radians( lat ) ) ) ) < 25 > ORDER BY distance > > Andrew -- You received this message because you are subscribed to the Google Groups "Google Maps API" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/google-maps-api?hl=.
