Reuse the result of a function in Where

Asked

Viewed 81 times

1

Hello, I am working with MYSQL, I wonder if I can use the result of a function in Where, calling by alias.

today I have to repeat the code, in case that GEO function

SELECT *,round(geo(-46.000000,-23.000000,latitude,longitude),3) AS distancia 
FROM view_empresas_pins 
where round(geo(-46.000000,-23.000000,latitude,longitude),3) <= 7

I’d like to do something like this:

SELECT *,round(geo(-46.000000,-23.000000,latitude,longitude),3) AS distancia 
FROM view_empresas_pins where distancia <= 7

where the result of the GEO function is used in Where, without I have to call again.

mysql

  • You could put one more SELECT around that by creating a subquerie, that’s another way of doing it, but that slows down performance because it generates a temporary table. I advise repeating the expression.

1 answer

3


You can use the function HAVING of MySQL, so you can work with the alias called in your query, the consultation would look like this:

SELECT 
     *,
    round(geo(-46.000000,-23.000000,latitude,longitude),3) AS distancia 
FROM view_empresas_pins 
HAVING distancia <= 7
  • 1

    Yes, it works in this case. Just be careful that it only works in Mysql. Having is to use with grouping, in this specific case mysql makes a group by implicit.

  • Excellent, gave it right. thank you very much.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.