-->
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.  [ 4 posts ] 
Author Message
 Post subject: group by on date fields
PostPosted: Wed Jan 28, 2004 11:47 am 
Newbie

Joined: Wed Jan 28, 2004 11:33 am
Posts: 4
Hi all,
Actually I need to perform a query returning sums of data aggregated on a given period according to a given date interval(per day, week or month). In SQL it would be something like: SELECT count(*), DATE_FORMAT(<date_column>, '%Y %m %d') toto FROM <table> GROUP BY toto;

I have 3 questions:
* Is it possible to do so with HQL? And how?
* Is it possible to use in HQL nataive SQL functions? And how?
* Is it possible to name a select variable (sucj as toto above)? And how? :)

Thanks for your work

Aldo


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jan 28, 2004 11:51 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
In HQL only the DATE_FORMAT() call would give you problems.


You may use any native SQL function in any HQL clause except the SELECT clause, which is "fussier" (I'll let David fill in the details).


No, there are no SELECT-clause aliases in HQL (you don't need them, actually).


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jan 28, 2004 12:00 pm 
Newbie

Joined: Wed Jan 28, 2004 11:33 am
Posts: 4
>No, there are no SELECT-clause aliases in HQL (you don't need them, actually).

For the following query:

select distinct ticket.userSessionId, device, count(device)
from Ticket as ticket, Device as device
where ticket.state.name = 'Hit'
and ticket.deliveryPhone = device
order by count(device) desc

the generated sql does not use the aliases:

select distinct device1_.DEVICE_ID as DEVICE_ID, device1_.NAME as NAME, device1_.WAP_UA as WAP_UA, device1_.ICON_URL as ICON_URL, device1_.MAX_FILE_SIZE as MAX_FILE5_, device1_.DELIVERY_TYPE_ID as DELIVERY6_, device1_.NOTIFICATION_TYPE_ID as NOTIFICA7_, device1_.SCREEN_WIDTH as SCREEN_W8_, device1_.SCREEN_HEIGHT as SCREEN_H9_, device1_.COLOR_DEPTH as COLOR_D10_, device1_.IMAGE_TYPE_ID as IMAGE_T11_, device1_.MAX_IMAGE_WIDTH as MAX_IMA12_, device1_.MAX_IMAGE_HEIGHT as MAX_IMA13_, device1_.NB_MIDI_CHANNELS as NB_MIDI14_, device1_.MAX_MIDI_SIZE as MAX_MID15_, device1_.MAX_IMAGE_SIZE as MAX_IMA16_, mstticket0_.USER_SESSION_ID as x0_0_, device1_.DEVICE_ID as x1_0_, count(device1_.DEVICE_ID) as x2_0_ from MST_TICKET mstticket0_, DEVICE device1_, REF_DATA_ITEM refdataite2_ where (refdataite2_.NAME='DeliveryFirstHit' and mstticket0_.STATE_ID=refdataite2_.REF_DATA_ITEM_ID)and(mstticket0_.DELIVERY_DEVICE_ID=device1_.DEVICE_ID ) order by count(device1_.DEVICE_ID)desc

And this query does not work whereas using aliases for the select clause would work...
Mayby there is an other way to do...


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jan 28, 2004 4:05 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Some versions of some database don't like this (eg. older versions of DB2/400 and perhaps some versions of MySQL). I'm not aware of any current versions of supported platforms which have a problem with this.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 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.