Pass a parameter to select case with group by in HQL
I have the following HQL(The Hibernate Query Language) query (which is executed on oracle db connection) to retrieve an aggregated sum, based on month
select sum(doc._masskg),
case when (month(doc._date) = month(:m)) then 'NOW' else 'BEFORE'
from Document doc
where month(doc._date) <= month(:m)
group by
case when (month(doc._date) = month(:m)) then 'N开发者_运维问答OW' else 'BEFORE' end
however, in the runtime I get Oracle exception NOT A GROUP BY EXPRESSION
if I replace :m with sysdate, the query works. How do I rewrite the query if m: is Date?
So, I have ended up replacing the parameter with an actual value:
String hql = "select sum(doc._masskg), "+
" case when (month(doc._date) = month(%1$s)) then 'NOW' else 'BEFORE' "+
"from Document doc "+
" where month(doc._date) <= month(%1$s) "+
"group by "+
" case when (month(doc._date) = month(%1$s)) then 'NOW' else 'BEFORE' end";
Iterator i = session.createQuery(String.format(HQL, "to_date('09.10.2011', 'DD.MM.YYYY')"))
.iterate();
精彩评论