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

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

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

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.