Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Implementing aggregate boolean functions in JPQL?
Implementing aggregate boolean functions in JPQL? [message #533496] Fri, 14 May 2010 09:28 Go to next message
Dean Ashby is currently offline Dean AshbyFriend
Messages: 4
Registered: July 2009
Junior Member
Hi,

I'm trying to implement the equivalent of an aggregate boolean AND function. The following query is based on pure SQL ideas taken from this article and although it shows what I'm trying to achieve, it's not valid JPQL:

SELECT  
    NEW BookingSummaryDto( b.id, b.bookingReference, SUM(i.price), MIN(CASE WHEN i.paid THEN 1 ELSE 0 END) )
FROM
    Booking AS b JOIN b.items AS i
GROUP BY
    b.id, i.bookingReference     


The other methods described in the article don't appear to be possible with JPQL either as they involve sub-queries in the from clause.

Can anyone suggest a way to achieve this in JPQL?

Thanks,

Dean



Re: Implementing aggregate boolean functions in JPQL? [message #533971 is a reply to message #533496] Mon, 17 May 2010 15:14 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You could try using EclipseLink 2.1/trunk as we have improved our JPQL support. Sub-queries in the from clause are still not supported through (you can add an enhancement request for this).

Otherwise, you could use a native SQL query.


James : Wiki : Book : Blog : Twitter
Re: Implementing aggregate boolean functions in JPQL? [message #534045 is a reply to message #533971] Mon, 17 May 2010 19:36 Go to previous message
Dean Ashby is currently offline Dean AshbyFriend
Messages: 4
Registered: July 2009
Junior Member
Thanks for your comments James. I did end up going the native SQL way in the end.
Previous Topic:When I set FirstResult and MaxResult how I can get the total ( count() )
Next Topic:No Persistence provider from dependency
Goto Forum:
  


Current Time: Sun Dec 21 04:14:30 GMT 2014

Powered by FUDForum. Page generated in 0.08732 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software