2 Replies Latest reply on Nov 26, 2007 8:00 PM by Terry

    EntityQuery Bug with Group by and Restriction?

    Terry Apprentice

      I 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]