Java Programming

0 down vote favorite
share [fb] share [tw]


considering I have Txn_Summ_Dec as a domain object (Entity) with all the fields used in the query how would I convert the SQL query below to Hibernate Query (ether HQL or Criteria api)..

What the query does: Given a date range as input the query counts distinct users grouped by date, eventdesc and left joins with all the dates in the date range so resultset contains user counts for all dates in the range for all events.

 select 
    d.dateInRange as dateval,
    eventdesc,
    nvl(td.dist_ucnt, 0) as dist_ucnt
from (
    select 
        to_date('03-NOV-2011','dd-mon-yyyy') + rownum - 1 as dateInRange
    from all_objects
    where rownum <= to_date('31-DEC-2011','dd-mon-yyyy') - to_date('03-NOV-2011','dd-mon-yyyy') + 1
) d
left join (
    select 
        currentdate,
        count(distinct(grauser_id)) as dist_ucnt,
        eventdesc 
    from
        txn_summ_dec

    group by currentdate, eventdesc 
) td on td.currentdate = d.dateInRange order by d.dateInRange asc

The query runs successfully on oracle returning the required result

Edited by: EJP on 30/01/2012 20:12: added {noformat}

{noformat} tags on your behalf, to make your code legible.