Coalesce equivalent in Hibernate Criteria query?

Question

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

In place of nvl(), you should be able to use coalesce(). This function is an SQL standard and should hopefully work.

At the time you had asked this, I realise that you mightn't have had this function available. nHibernate Support ticket [NH-2711] covered the inclusion of the COALESCE function.