Coalesce equivalent in Hibernate Criteria query?

Question

Hi everyone,

I want to write the following query as a Hibernate Criteria query:

   select 
      to_char(nvl(ol.updated_datetime, ol.created_datetime), 'dd/mm/yyyy'), sum(discount_price)
   from order_line ol
   where nvl(ol.updated_datetime, ol.created_datetime) between to_date('05-may-10') and to_date('30-may-10') 
   group by to_char(nvl(ol.updated_datetime, ol.created_datetime), 'dd/mm/yyyy')

But I'm not sure how to convert the nvl function to a Criteria query equivalent. I realise that HQL has a coalesce expression but I want to write it as a Criteria query.

Any advice would be really appreciated!

Edit: If anyone can provide an HQL query that does the above that could be my solution as well.

Answer 1

See Restrictions.sqlRestriction in Hibernate Criteria.