Dear,
I've a big trouble.
If I write a SQL query as follow:
Code:
SELECT
coalesce(
(select sum(field1) from table t2 where t2.fk = t.id and other conditions)
,0)
FROM table t
In HQL I try to convert in follow query:
Code:
SELECT new MyObject(
coalesce(
(select sum(t2.field1) from table t2 where t2.fk = t.id and other conditions)
,0)
)
FROM table t
But the HQL parser goes in error because I can't use coalesce with a sub select include. The only way accepted by Hibernate is the follow:
Code:
(SELECT coalesce(SUM(t2.field1), 0) from table t2 where t2.fk = t.id other conditions)
But is not correct because, in SQL
if all conditions of subquery are satisfied and the sum is null coalesce returns 0, BUT if some conditions are not satisfied all the select is null and only an external coalesce respect the query can put 0 value.
Finally, MyObject constructor don't want NULL value as parameter, so I don't know how to resolve this brain teaser!!!
Help me please!
Login
FAQ
Search