Chris, What happens if you rewrite the query like this:
SELECT dl.costcentre, sum(dl.bwCount), dl.bwRate, sum(dl.bwCount * dl.bwRate), sum(dl.clrCount), dl.clrRate, sum(dl.clrCount * dl.clrRate) FROM EquitracDataLoadLine as dl where dl.batchId = '00000127C9E533DC' and dl.contract.uniqueid = '0000012444686ECC' and dl.asset is not null group by dl.costcentre, dl.bwRate, dl.clrRate order by dl.costcentre The result should be identical and you fullfill the requirement of having non-aggregates in GROUP BY. The requirement comes from the JPA spec: "The requirements for the SELECT clause when GROUP BY is used follow those of SQL: namely, any item that appears in the SELECT clause (other than as an aggregate function or as an argument to an aggregate function) must also appear in the GROUP BY clause." Cheers, Milosz > I have some queries that I have been using in my app for about a year now > but since I started using Beta 3 they fail validation because OpenJPA wants > me to include all of my select columns in the group by. Since most of the > select items are SUM(blah) I can't really do that and also there is no need > for me to do it. Here is my query: > > > > SELECT dl.costcentre, sum(dl.bwCount), dl.bwRate, sum(dl.bwCount * > dl.bwRate), sum(dl.clrCount), dl.clrRate, sum(dl.clrCount * dl.clrRate) > FROM EquitracDataLoadLine as dl where dl.batchId = '00000127C9E533DC' and > dl.contract.uniqueid = '0000012444686ECC' and dl.asset is not null > > group by dl.costcentre order by dl.costcentre > > > > OpenJPA complains: > > > > > > <openjpa-2.0.0-beta3-r422266:926797 nonfatal user error> > org.apache.openjpa.persistence.ArgumentException: Your query on type "class > com.trm.dataload.linetypes. > > EquitracDataLoadLine" with filter "SELECT dl.costcentre, sum(dl.bwCount), > dl.bwRate, sum(dl.bwCount * dl.bwRate), sum(dl.clrCount), dl.clrRate, > sum(dl.clrCount * dl.clrRate) FROM EquitracDataLoadLine as dl where > dl.batchId = '00000127C9E533DC' and dl.contract.uniqueid = > '0000012444686ECC' and dl.asset is not null group by dl.costcentre order by > dl.costcentre" is invalid. Your select and having clauses must only include > aggregates or values that also appear in your grouping clause. at > org.apache.openjpa.kernel.ExpressionStoreQuery$AbstractExpressionExecutor$Va > lidateGroupingExpressionVisitor.enter(ExpressionStoreQuery.java:533) > > at org.apache.openjpa.kernel.exps.Val.acceptVisit(Val.java:119) > > at > org.apache.openjpa.kernel.ExpressionStoreQuery$AbstractExpressionExecutor$Va > lidateGroupingExpressionVisitor.validate(ExpressionStoreQuery.java:514) > > at > org.apache.openjpa.kernel.ExpressionStoreQuery$AbstractExpressionExecutor.va > lidate(ExpressionStoreQuery.java:327) > > at org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:589) > > at > org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerIm > pl.java:976) > > at > org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerIm > pl.java:962) > > at > org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerIm > pl.java:98) > > > > There is no "having" clause, so I'm not sure what it is complaining about. > Not sure how this is non-fatal either because it's throwing an exception > which I can catch but of course I can't execute the query, to me that is > fatal. > > > > Switching back to OpenJPA 1.2.1 and the error goes away. Back to OpenJPA > Beta 3 and the error comes back. > > > > > > Thanks for any advice. > > > > Chris > > > >
