Is there something wrong with this query?

SELECT id, ( 6371 * acos(cos(radians(?1)) * cos(radians(latitud)) * cos(radians(longitud) - radians(?2)) + sin(radians(?1)) * sin(radians(latitud)))) AS distance FROM usuario_señales HAVING (distance < 0.1 AND ancho <= ?3) ORDER BY distance;

I have a web server in spring boot and I am trying to get the id of the rows in a radius of 1 metes according to the latitud and longitude and with the attribute ancho smaller than the one I pass:

@Query(nativeQuery = true, value="SELECT id,( 6371 * acos(cos(radians(?1)) * cos(radians(latitud)) * cos(radians(longitud) - radians(?2)) + sin(radians(?1)) * sin(radians(latitud)))) AS distance FROM usuario_señales HAVING (distance < 0.1 AND ancho <= ?3) ORDER BY distance")

The error that I get is the following:

java.sql.SQLSyntaxErrorException: Unknown column ‘ancho’ in ‘having clause’

There is something wrong with the having clause and how I use it. Any clue? Thank you in advanced.

## Advertisement

## Answer

From the description of your question, it does not look like you want an aggregation query. Instead, you are trying to use the `having`

clause to avoid repeating the lengthy computation in the `where`

clause: MySQL supports that (even without a `group by`

clause), but in that case will let you use only columns from the `select`

clause in the `having`

clause.

You don’t need to use the `having`

clause to filter on `ancho`

, you can use a regular `where`

clause. I think the query you meant to write was:

SELECT id, ( 6371 * acos(cos(radians(?1)) * cos(radians(latitud)) * cos(radians(longitud) - radians(?2)) + sin(radians(?1)) * sin(radians(latitud)))) AS distance FROM usuario_señales WHERE ancho <= ?3 HAVING distance < 0.1 ORDER BY distance;

I am not fan of this syntax, which relies on MySQL extension to the standard SQL. I would use a subquery instead:

SELECT * FROM ( SELECT id, ( 6371 * acos(cos(radians(?1)) * cos(radians(latitud)) * cos(radians(longitud) - radians(?2)) + sin(radians(?1)) * sin(radians(latitud)))) AS distance FROM usuario_señales WHERE ancho <= ?3 ) t WHERE distance < 0.1 ORDER BY distance;