I am using HQL to add a few fields from a table. Here is an example:

select (foo.valueA + foo.valueB + foo.valueC) from Foo foo where foo.bar.date = :date

I can then get the resulting value doing something like:



If foo.valueA = 10 and foo.valueB = 15 and foo.valueC = 30 then this query returns 55 : excellent!

If foo.valueA = 10 and foo.valueB = 15 and foo.valueC = null then this query returns null and not 25: NOT excellent!

What I would hope is that in this second example the query would return the value '25' as that is the sum of those three fields as best can be determined. Is this type of behavior possible? Any help is appreciated.

Thanks,

Rob

Hi,

Before using hibernate, I would recommend you to learn SQL otherwise you will get more serious problems...

NULL!=0. Therefore, the result you get is correct in term of sql.
Try (on oracle) SELECT 1 + 2 + NULL FROM DUAL; is not the same as SELECT 1 + 2 + 0 FROM DUAL;
Rgds

Thanks for the tip.

I fully understand that NULL != 0, that is why I suggested that I would like the 2nd query to return the sum of the 3 values as best can be determined. That is also why I asked if this particular type of behavior is possible and why I didn't suggest that this type of behavior should necessarily be automatically expected.

I'm not sure if this will work in HQL or not, but it seems to work in SQL on MySQL. If it doesn't work, maybe it will help lead you down the correct path.

select if(valueA is null, 0, valueA) + if (valueB is null, 0, valueB) + if (valueC is null, 0, valueC) ...

Good luck!

I found a real solution to this problem that should work in HQL, the coalesce() function. It takes a list of parameters and returns the first non-null value.

select coalesce(valueA, 0) + coalesce(valueB, 0) + coalesce(valueC, 0) from ...

Thanks -- this solves the problem!