EntityQuery Bug with Group by and Restriction?
terryb Nov 26, 2007 11:53 AMI am not sure if, im doing it the wrong way or it's a bug.
I'm attempting aggregate query in using EntityQuery and Restrictions following Seam-Gen example. When query is executed, the 'restrictions's parameters get appended to Group By clause rather than Where clause. Please see attached query, and code.
Is there any other way of doing this?
... @Name("paymentReconciliation") public class PaymentReconciliation extends EntityQuery { private static final String[] RESTRICTIONS = { "commweb.settleDate >= #{paymentReconciliation.settlementDateFrom}", "commweb.settleDate <= #{paymentReconciliation.settlementDateTo}", }; private Date settlementDateFrom = null; private Date settlementDateTo = null; private boolean executeSearch = false; @Override public String getEjbql() { return "select commweb.settleDate, " + "sum(CASE WHEN client.qtiscApplicantType=2 THEN 1 ELSE 0 END) as TotalAppsTee, " + "sum(CASE WHEN client.qtiscApplicantType=3 THEN 1 ELSE 0 END) as TotalAppsNonTee, " + "sum(CASE WHEN client.qtiscApplicantType=2 THEN commweb.amount ELSE 0 END) as TotalAmountTee, " + "sum(CASE WHEN client.qtiscApplicantType=3 THEN commweb.amount ELSE 0 END) as TotalAmountNonTee, " + "count(application.id) as TotalApps, " + "sum(commweb.amount) as TotalAmount " + "from Client client " + "join client.applications application " + "join application.ttransactions ttransaction " + "join ttransaction.commweb commweb " + "where ttransaction.paymentMethod='" + Constant.Application.PaymentMethod.ONLINE_CREDIT_CARD + "' " + "and commweb.status = 'approved' " + "group by commweb.settleDate " ; } ... Error Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: and near line 1, column 751 ... Query Produced by Seam select commweb.settleDate, sum(CASE WHEN client.qtiscApplicantType=2 THEN 1 ELSE 0 END) as TotalApp sTee, sum(CASE WHEN client.qtiscApplicantType=3 THEN 1 ELSE 0 END) as TotalAppsNonTee, sum(CASE WHEN client.qtiscApplicantType=2 THEN commweb.amount ELSE 0 END) as TotalAmountTee, sum(CASE WHEN client.qtiscApplicantType=3 THEN commweb.amount ELSE 0 END) as TotalAmountNonTee, count(application.id) as TotalApps, sum(commweb.amount) as TotalAmount from au.edu.tisc.entity.Client client join client.applications application join application.ttransactions ttransaction join ttransaction.commweb commweb where ttransaction.paymentMethod='online-credit-card' and commweb.status = 'approved' group by commweb.settleDate and commweb.settleDate >= :el1 and commweb.settleDate <= :el2 order by settleDate asc]