Hibernate exception. QuerySyntaxException: unexpected token: HAVING

Question

I try use this HQL query:

   Result.find("SELECT c, ( 3959 * acos( cos( radians(?) ) * "+
    "cos( radians( c.latitude ) ) *"+
    "cos( radians( c.longitude ) - radians(?) ) +"+
    "sin( radians(?) ) * sin( radians( c.latitude ) ) ) ) " +
    "AS distance FROM City c HAVING distance < ? ORDER BY distance ASC",
    latitude, longitude, latitude, radius).fetch();

But in result:

IllegalArgumentException occured : org.hibernate.hql.ast.QuerySyntaxException: unexpected token: HAVING near line 1, column 204 [SELECT c, ( 3959 * acos( cos( radians(?) ) * cos( radians( c.latitude ) ) *cos( radians( c.longitude ) - radians(?) ) +sin( radians(?) ) * sin( radians( c.latitude ) ) ) ) AS distance FROM models.City c HAVING distance < ? ORDER BY distance ASC]

Vikram · Accepted Answer · 2012-04-12 06:29:09Z

1

try changing HAVING to WHERE, in your query.

answered Apr 12, 2012 at 6:29

Vikram

8,32335 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Anthony Tsivarev Over a year ago

Now Unknown column 'distance' in 'where clause'

Vikram Over a year ago

distance is alias for the column, you have to put the whole calculation as in select, in where clause as well. instead of column alias

Anthony Tsivarev Over a year ago

But then I can not sort the list of cities.

Anthony Tsivarev Over a year ago

Hm... Unknown column 'col_0_0_' in 'order clause'

Anthony Tsivarev Over a year ago

This query work: SELECT c, ( … ) AS distance FROM City c ORDER BY distance ASC. But without WHERE

stacker · Accepted Answer · 2012-04-12 06:29:41Z

1

The keyword HAVING is only allowed if you use GROUP BY (think of having like WHERE for GROUPS). See the reference manual for SELECT syntax.

answered Apr 12, 2012 at 6:29

stacker

69.1k29 gold badges143 silver badges214 bronze badges

3 Comments

Anthony Tsivarev Over a year ago

Yes, you're right. Now I've corrected the 'having' to 'where'. And got an error: Unknown column 'distance' in 'where clause'

stacker Over a year ago

@AntonTsivarev You need to place the expression in a subquery to refer to distance.

Remy Mellet Over a year ago

@stacker, you cannot use a subquery expression in the from : "Note that HQL subqueries can occur only in the select or where clauses." docs.jboss.org/hibernate/core/4.3/manual/en-US/html_single/…

Remy Mellet · Accepted Answer · 2015-11-10 20:15:04Z

1

The only way is to use a native query because :

subselect is not possible : "Note that HQL subqueries can occur only in the select or where clauses." http://docs.jboss.org/hibernate/core/4.3/manual/en-US/html_single/#queryhql-subqueries
having is not possible without a group by
where is not possible neither

Java code :

@Query(value = "SELECT s.*,\n" +
            "  (\n" +
            "    3959 * acos(\n" +
            "        cos(radians(s.lat))\n" +
            "        * cos(radians(:lat))\n" +
            "        * cos(radians(:lon) - radians(s.lon))\n" +
            "        + sin(radians(s.lat))\n" +
            "          * sin(radians(:lat))\n" +
            "    )\n" +
            "  ) AS distance\n" +
            "FROM Stop s\n" +
            "HAVING distance < 30\n" +
            "ORDER BY distance asc", nativeQuery = true)
    List<Stop> findClosestStops(@Param("lat") Double lat, @Param("lon") Double lon);

An alternative is to return a list of object containg the city + distance, but no where is possible, but you can limit the resultset

answered Nov 10, 2015 at 20:15

Remy Mellet

1,80522 silver badges25 bronze badges

1 Comment

Vivek Garg Over a year ago

Worked for me. I was missing nativeQuery=true param in my code. Just for those who are trying to execute the query in JPA then put, nativeQuery = true and try.

blackuprise · Accepted Answer · 2012-04-12 06:31:20Z

0

Hm, what should be "?" parameter?

If true you should use it like

HAVING distance < :distance

and use

query.setParameter("distance", 50);

answered Apr 12, 2012 at 6:31

blackuprise

4506 silver badges26 bronze badges

Comments

Shehzad · Accepted Answer · 2012-04-12 14:14:43Z

0

try

 select dis.c,dis.distance(
    SELECT c,
     ( 3959 * acos( cos( radians(?) ) *  cos( radians( c.latitude ) ) * 
cos( radians( c.longitude ) - radians(?) ) + sin( radians(?) ) *
 sin( radians( c.latitude ) ) ) )  AS distance FROM City c  ) dis 
where dis.distance<? order by dis.distance ASC

answered Apr 12, 2012 at 14:14

Shehzad

2,95219 silver badges21 bronze badges

Collectives™ on Stack Overflow

Hibernate exception. QuerySyntaxException: unexpected token: HAVING

5 Answers 5

5 Comments

3 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related