Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

IFNULL equivalent in Hibernate Query Language?

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?

like image 480
Michał Bendowski Avatar asked Nov 01 '09 16:11

Michał Bendowski


People also ask

What is the name of hibernates own query language?

HQL or Hibernate Query Language is the object-oriented query language of Hibernate Framework.

How do you write NOT null in HQL query?

No. You have to use is null and is not null in HQL. Save this answer.

What is NVL in HQL?

The equivalent to the nvl command in HQL is the coalesce command. coalesce(a,b) will return a if a is not null, otherwise b .


2 Answers

COALESCE is the official equivalent.

It returns the first non-null of its arguments.

Example:

    COALESCE(id_pati, 0)

Link Wikipedia

like image 69
KLE Avatar answered Oct 14 '22 22:10

KLE


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
like image 43
dmonlord Avatar answered Oct 14 '22 21:10

dmonlord