Re: [eclipselink-users] Help with having clause

[James Sutherland <jamesssss@xxxxxxxxx>]

You can use a sub-select for this.

Something like: (JPQL)

Select object from MyObject object where object.externalIdentifier = :id and
object.organization = :org and object.sourceRecordDate = (Select
Max(inner.sourceRecordDate) from MyObject inner where
inner.externalIdentifier = :id and inner.organization = :org and
inner.sourceRecordDate <= :date)


cmathrusse wrote:
> 
> Well, if my situation was that simple I would use em.find(...) My 
> apologies for not explaining it better.
> 
> ID               discriminator     SOURCE_RECORD_DATE            ADDRESS_1 
>          EXTERNAL_IDENTIFIER  ORGANIZATION
> 19766            BillTo          2009-08-12 10:34:43.893         4567 Test 
> Dr.              31295-90                AMER
> 19775            BillTo          2009-08-20 16:18:19.27          4567 Test 
> Dr.              31295-90                AMER
> 19776            BillTo          2009-08-20 16:55:07.02          4567 Test 
> Dr.              31295-90                AMER
> 
> 
> Looking at the data, the records are virtually identical all except for 
> the SOURCE_RECORD_DATE. So basically I'm attempting a SELECT based upon 
> the records EXTERNAL_IDENTIFIER and the ORGANIZATION, which will match all 
> 3 records. So given a date of 2009-08-20 16:30:00.00, I would like to 
> retrieve the record that has a SOURCE_RECORD_DATE that has the greatest 
> value that is equal to or less than the supplied date, that would be the 
> row that has id 19775. If my understanding is correct, the only way that I 
> am going to retrieve the single matching row is to use a HAVING clause to 
> further filter the matching rows.
> 
> Thanks...
> Chris 
> 
> 


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://www.nabble.com/Help-with-having-clause-tp25138774p25151372.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.