-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 
  Previous topic | Next topic 
Author Message
pooter8d
 Post subject: Query parser error
PostPosted: Fri May 16, 2008 3:09 pm 
Beginner
Beginner

Joined: Wed Mar 05, 2008 10:32 am
Posts: 48
I'm getting the following the following error:

12:03:48,427 ERROR PARSER:33 - line 1:222: unexpected token: HAVING

this is my query string

SELECT id, ( 3959 * acos( cos( radians(41.017124) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-74.24237) ) + sin( radians(41.017124) ) * sin( radians( lat ) ) ) ) AS distance FROM mydb.mytable HAVING distance < 5 ORDER BY distance;

it happens at this code:
Code:
Query q =  (Query) getSession().createQuery(query).uniqueResult();


I was getting the same error if i didnt include the mydb before the table, but why its not likeing the keyword HAVING i have no clue.
Top
 Profile  
 
treuss
 Post subject:
PostPosted: Fri May 16, 2008 3:42 pm 
Newbie

Joined: Fri May 16, 2008 3:40 pm
Posts: 13
It should be WHERE, not HAVING. For normal conditions that apply to a single row in the table, use WHERE. For conditions that apply to a group of rows, ordered by a group by clause, use HAVING.
Top
 Profile  
 
pooter8d
 Post subject:
PostPosted: Fri May 16, 2008 4:21 pm 
Beginner
Beginner

Joined: Wed Mar 05, 2008 10:32 am
Posts: 48
This is incorrect.

Since distance is not a column, a where clause cannot be applied to it.

As you see below..
Code:
mysql> SELECT id, ( 3959 * acos( cos( radians(41) ) * cos( radians( lat ) ) * co
s( radians( lng ) - radians(-70) ) + sin( radians(41) ) * sin( radians( lat ) )
) ) AS distance FROM venueent HAVING distance < 250 ORDER BY distance;
+----+-----------------+
| id | distance        |
+----+-----------------+
|  2 | 208.96648178691 |
|  4 | 209.35520222501 |
|  1 |  209.3636726854 |
|  5 | 209.86840227878 |
|  3 | 209.89832530515 |
|  6 | 210.09721899478 |
+----+-----------------+
6 rows in set (0.00 sec)


while using 'Where'
Code:
mysql> SELECT id, ( 3959 * acos( cos( radians(41) ) * cos( radians( lat ) ) * co
s( radians( lng ) - radians(-70) ) + sin( radians(41) ) * sin( radians( lat ) )
) ) AS distance FROM venueent WHERE distance < 250 ORDER BY distance;
ERROR 1054 (42S22): Unknown column 'distance' in 'where clause'


Also note these results from the debug console...

13:20:20,156 WARN JDBCExceptionReporter:77 - SQL Error: 1054, SQLState: 42S22
13:20:20,156 ERROR JDBCExceptionReporter:78 - Unknown column 'distance' in 'where clause'
Top
 Profile  
 
pooter8d
 Post subject:
PostPosted: Fri May 16, 2008 4:29 pm 
Beginner
Beginner

Joined: Wed Mar 05, 2008 10:32 am
Posts: 48
OK it seems that Query object needs a GROUP BY before the HAVING... but i get this now...

Code:
Hibernate:
    /* SELECT
        id,
        ( 3959 * acos( cos( radians(41.017124) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-74.24237) ) + sin( radians(41.017124) ) * sin( radians( lat ) ) ) ) AS distance
    FROM
        VenueEnt
    GROUP BY
        id
    HAVING
        distance < 5
    ORDER BY
        distance */ select
            venueent0_.id as col_0_0_,
            3959*acos(cos(radians(41.017124))*cos(radians(lat))*cos(radians(lng)-radians(-74.24237))+sin(radians(41.017124))*sin(radians(lat))) as col_1_0_
        from
            VenueEnt venueent0_
        group by
            venueent0_.id
        having
            distance<5
        order by
            distance
13:26:43,445  WARN JDBCExceptionReporter:77 - SQL Error: 1054, SQLState: 42S22
13:26:43,445 ERROR JDBCExceptionReporter:78 - Unknown column 'distance' in 'order clause'


I see that in the actually code the new col name for my equation is col_1_0..... but WHY is that?
Top
 Profile  
 
pooter8d
 Post subject:
PostPosted: Fri May 16, 2008 4:38 pm 
Beginner
Beginner

Joined: Wed Mar 05, 2008 10:32 am
Posts: 48
.... ignore
Top
 Profile  
 
treuss
 Post subject:
PostPosted: Fri May 16, 2008 5:11 pm 
Newbie

Joined: Fri May 16, 2008 3:40 pm
Posts: 13
pooter8d wrote:
This is incorrect.

Since distance is not a column, a where clause cannot be applied to it.

The column certainly can be referred to in the where clause. It just cannot be referred to by its alias "distance". So one way to write this is the cumbersome:

Code:
SELECT id,
       (3959 * acos( cos( radians(41) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-70) ) + sin( radians(41) ) * sin( radians( lat ) ) ) ) AS distance
FROM venueent
WHERE 3959 * acos( cos( radians(41) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-70) ) + sin( radians(41) ) * sin( radians( lat ) ) ) < 250
ORDER BY 3959 * acos( cos( radians(41) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-70) ) + sin( radians(41) ) * sin( radians( lat ) ) )


The second possibility is querying from the query result. Something like:

Code:
SELECT id, distance from
( SELECT id, ( 3959 * acos( cos( radians(41) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-70) ) + sin( radians(41) ) * sin( radians( lat ) ) ) ) AS distance
  FROM venueent
)
WHERE distance < 250 ORDER BY distance;


mysql's interpretation of the SQL standard is at times very relaxed. One example is allowing having without a prior group by. Another example is queries without a from clause.


Last edited by treuss on Sat May 17, 2008 6:19 am, edited 2 times in total.

Top
 Profile  
 
pooter8d
 Post subject:
PostPosted: Fri May 16, 2008 9:50 pm 
Beginner
Beginner

Joined: Wed Mar 05, 2008 10:32 am
Posts: 48
By placing the alias after the table name i was able to get rid of the error. Unfortunately i'm not sure if i'm getting the correct results becuase i'm still getting an incorrect query.

Code:
select
            venueent0_.id as col_0_0_,
            3959*acos(cos(radians(40.755367))*cos(radians(lat))*cos(radians(lng)-radians(-73.98749))+sin(radians(40.755367))*sin(radians(lat))) as col_1_0_
        from
            VenueEnt venueent0_
        group by
            venueent0_.id
        having
            venueent0_.id<10
        order by
            venueent0_.id ASC limit ?


from
Code:
query += "SELECT id, ( 3959 * acos( cos( radians(" + lat +") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(" + lng + ") ) + sin( radians(" + lat + ") ) * sin( radians( lat ) ) ) ) FROM VenueEnt AS distance GROUP BY id HAVING distance < " + maxdistance + " ORDER BY distance ASC";


Why is it sorting by id? I tried grouping by distance, but that yielded the same results. Why is my query translation not working properly?
Top
 Profile  
 
treuss
 Post subject:
PostPosted: Sat May 17, 2008 6:17 am 
Newbie

Joined: Fri May 16, 2008 3:40 pm
Posts: 13
You don't really want to group, do you? So I suggest you get rid of the group by and the having clause and go from there.
Top
 Profile  
 
pooter8d
 Post subject:
PostPosted: Tue May 20, 2008 2:53 pm 
Beginner
Beginner

Joined: Wed Mar 05, 2008 10:32 am
Posts: 48
I solved this by using createSQLQuery instead of createQuery. This doesn't do any translating.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.