Hi,

I'm translating an SQL query to HQL (for a MySQL DB). It uses a series of outer joins and the ifnull function to translate nulls to known values in order that every row is returned (not just those containing non-nulls):

Does hibernate support something like the ifnull function or do I have to uses a native SQL query in this instance:

The original SQL looks something like:

select
u.dsivId, ifnull(s.semaineId,"0000W0"), sc.saisieActivityId,
sum(ifnull(sc.charge,0))
from users u
left outer join
saisies s on s.userId = u.dsivId
left outer join
saisie_activities sa on sa.saisieId = s.id
left outer join
saisie_charges sc on sc.saisieActivityId = sa.id
group by
s.semaineId, u.dsivId, sc.saisieActivityId
order by
u.dsivId, s.semaineId


TIA

Tom

You can use native SQL functions in HQL.

Thanks for your reply michael.

I know I can call native SQL but I would like to know if I can do an ifnull with "pure" HQL. Shouldn't we avoid using native SQL if at all possible ?

Tom

You can just use ifnull() in your HQL query, if your DB supports it

It doesn't seem to. Here's my HQL query with the ifnull in. This query works without the ifnull bit (except that I have a null value instead of 16 of course).

The error I get is :



Thanks for your help with this

Solution is to either use COALESCE(...) - some say NULLIF(..)
see http://stackoverflow.com/questions/1657637/ifnull-equivalent-in-hibernate-query-language

or use CASE WHEN ... THEN ... ELSE ... END (worked in my case)
see http://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/queryhql.html#queryhql-expressions