Re: [eclipselink-users] SUM in HAVING clause

[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