-->
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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Having clause without a group by clause in HQL?
PostPosted: Thu Mar 11, 2010 3:52 pm 
Newbie

Joined: Mon Feb 23, 2009 11:16 am
Posts: 17
Hi,

I've been putting together a schema with indexes and such and the following query

Code:
select * from stringfixmessagetick where seqNum >= 10591263 having timestamp > Timestamp('2010-03-04') and channelid = 111 limit 10000


is something I've been using in a MySQL query app. For detail, I'm using where with a separate having because this way MySQL uses the index I have created on "seqNum" instead of my index on "(channelId, timestamp)."

Now when I execute the following HQL statement:

Code:
   
Query query = session
   .createQuery("from StringFixMessageTick "
   + "where seqNum >= "
   + seqNum
   + " having channelId = " + channelId
   + " and timestamp >= " + date);


I get this error

Code:
org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: having near line 1, column 82 [from com.icarus.common.fix.messages.ticks.StringFixMessageTick where seqNum >= 4 having channelId = 111 and timestamp >= 3];


Is there a particular reason HQL doesn't support this?

If I can't do this query without a group by, what would be the simplest (non result modifying) group by I could add? I tried "group by id" but that didn't seem to work...

Thanks in advance for any help.


Top
 Profile  
 
 Post subject: Re: Having clause without a group by clause in HQL?
PostPosted: Thu Mar 11, 2010 4:38 pm 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
That was the weirdest thing I have seen for a long time... but then MySQL do have some weird and non-standard way of doing things sometimes. I would say forget it or use an SQL query.


Top
 Profile  
 
 Post subject: Re: Having clause without a group by clause in HQL?
PostPosted: Fri Mar 12, 2010 3:19 pm 
Newbie

Joined: Mon Feb 23, 2009 11:16 am
Posts: 17
I tried SQLQuery but from the looks of it the way I'm using having (as a secondary where) is not appropriate for SQL.

Instead I removed one of my indexes and now I'm only using one... this way my more frequent queries always run in sub-second speed while my less important queries take 30+ seconds... but hey, gotta compromise sometimes.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

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.