IFNULL equivalent in Hibernate Query Language?

Question

I'm trying to write an HQL query which will calculate an average rating for an item. I want the query to return 0 instead of null when there are no rating for a given item - so that I can use my query as a subquery. So is it possible? Is there an HQL equivalent of IFNULL or NVL?

Answer 1

COALESCE is the official equivalent.

It returns the first non-null of its arguments.

Example:

    COALESCE(id_pati, 0)

Link Wikipedia

Answer 2

Nhibernate docs are out of date. Check http://docs.jboss.org/hibernate/stable/core/reference/en/html/queryhql.html

If nothing works you can try:

select 
   case 
     when something is not NULL then 0
     else 
   end