RE: [eclipselink-users] SUM in HAVING clause

[DOUGLAS.CLARKE@xxxxxxxxxx]

Here is the link I use to open a bug:

 

https://bugs.eclipse.org/bugs/enter_bug.cgi?product=EclipseLink

-----Original Message-----
From: Jakob Braeuchi [mailto:jbraeuchi@xxxxxx]
Sent: Tuesday, July 15, 2008 3:16 PM
To: EclipseLink User Discussions
Subject: Re: [eclipselink-users] SUM in HAVING clause

hi tom,

unfortunately the link to the bug report http://www.eclipse.org/eclipselink/bugs is dead.

jakob

2008/7/15, Tom Ware <tom.ware@xxxxxxxxxx>:

Hi Jakob,

 Here's what is says in the JPA Spec (section 4.7)

"The HAVING clause must specify search conditions over the grouping items or aggregate functions that apply to grouping items."

 As I mentioned in an earlier email, I can definitely see the argument for loosening those restrictions.  Please feel free to enter this bug in bugzilla so we can address it.

-Tom

Jakob Braeuchi wrote:

hi tom,

i think it does not make sense to group by sum(p.betrag).

the sample is like an order (kauf) with it's items (posten). and i want
to find all orders with orderdate between :datVon and :datBis and the sum of the amount of the items between the two amounts.
is there a better way to build the query ?

this query runs without problems on hibernate-jpa and open-jpa.

jakob


Tom Ware schrieb:

Hi Jakob,

 The exception you are seeing is indicating that you can only use HAVING with things that are described in the GROUP BY section of the query.

i.e. To use SUM(p.betrag) in the having clause, either SUM(p.betrag) or p.betrag must appear in the GROUP BY clause.

-Tom

Jakob Braeuchi wrote:

hi,

i just downloaded the new eclipselinks 1.0. afaik it's based on oracle toplink and it still has problems with SUM in the HAVING clause.

the following query:

select new ch.brj.ekv.type.KaufShort(k.id, k.datum, k.einkaufer.name, SUM(p.betrag))
from Kauf k, in(k.posten) p
where k.datum between :datVon and :datBis
group by k.id, k.datum, k.einkaufer.name
having SUM(p.betrag) between :betrVon and :betrBis
order by k.datum asc

results in an error with this message:

"The HAVING clause must specify search conditions over the grouping items or aggregate functions that apply to grouping items."

the only item i use in the having-clause is the aggregate SUM(p.betrag).
could you please tell me what's wrong here ? does it violate the JPQL spec ?

btw this query works with hibernate.

jakob

ps: i have posted the same question in the oracle toplink forums in 2006 and 2007 (google for toplink having sum)
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users