[jboss-user] [JBoss Seam] - EntityQuery Bug with Group by and Restriction?

[terryb]

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

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4107796#4107796

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4107796