开发者

Pass a parameter to select case with group by in HQL

开发者 https://www.devze.com 2023-01-19 11:29 出处:网络
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

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();  
0

精彩评论

暂无评论...
验证码 换一张
取 消