openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Joel Halbert <j...@su3analytics.com>
Subject Re: count and 'group by' in jpql
Date Thu, 25 Nov 2010 10:09:55 GMT
Hi Chris,

>  JPA 2 is a pain because it forces us to only group by stuff in the select
>  clause,  some DBA propeller head negotiate that stupid rule into the spec.

Yes, i think that's the problem here. I've had to 'go native' on this one...

Joel


On 23/11/10 14:48, C N Davies wrote:
> Wouldn't you group by f or select by f.lastResp ?
>
>
> JPA 2 is a pain because it forces us to only group by stuff in the select
> clause,  some DBA propeller head negotiate that stupid rule into the spec.
>
> Chris
>
> -----Original Message-----
> From: Joel Halbert [mailto:joel@su3analytics.com]
> Sent: Wednesday, 24 November 2010 1:14 AM
> To: users@openjpa.apache.org
> Subject: count and 'group by' in jpql
>
> Hi All,
>
> A group by query along the following lines works as I expect in native SQL:
>
> select *,count(*) from FRONTIER group by LAST_RESP;
>
> Given the following data in FRONTIER
>
> ------------------------------
> COL1 | COL2 | LAST_RESP
> ------------------------------
> a | b | 200
> c | d | 200
> d | e | 400
> -------------------------------
>
> I get the results:
>
> a,b,200, 2
> d,e,400, 1
>
>
> If i try the same in JPQL:
>
> Query q = em.createQuery("select f, count(f) from GlobalFrontier f group by
> f.lastResp", GlobalFrontier.class);
>
> I get the following exception:
>
> Exception in thread "main"<openjpa-2.0.1-r422266:989424 nonfatal user
> error>  org.apache.openjpa.persistence.ArgumentException: Your query on
> type "class com.su3analytics.crawler.model.GlobalFrontier" with filter
> "select f, count(f) from GlobalFrontier f group by f.lastResp" 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)
>
>
> Is this a correct?
>
> Thanks,
> Joel
>
>
>
>
>


Mime
View raw message