Re: [eclipselink-users] SUM in HAVING clause

[Tom Ware <tom.ware@xxxxxxxxxx>]

Try going to www.eclipse.org/eclipselink and clicking on "Bugs" on the left side 
of the page.

Else:

Use the "New" option at the top of the following page and choose "RT" and the 
"EclipseLink":

https://bugs.eclipse.org/bugs/query.cgi

-Tom

Jakob Braeuchi wrote:
> 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 <mailto: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 <http://k.id>,
>                 k.datum, k.einkaufer.name <http://k.einkaufer.name>,
>                 SUM(p.betrag))
>                 from Kauf k, in(k.posten) p
>                 where k.datum between :datVon and :datBis
>                 group by k.id <http://k.id>, k.datum, k.einkaufer.name
>                 <http://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
>                 <mailto:eclipselink-users@xxxxxxxxxxx>
>                 https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
>             _______________________________________________
>             eclipselink-users mailing list
>             eclipselink-users@xxxxxxxxxxx
>             <mailto:eclipselink-users@xxxxxxxxxxx>
>             https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
>         _______________________________________________
>         eclipselink-users mailing list
>         eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@xxxxxxxxxxx>
>         https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
>     _______________________________________________
>     eclipselink-users mailing list
>     eclipselink-users@xxxxxxxxxxx <mailto: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