Re: [eclipselink-users] Help with having clause

[Christopher.Mathrusse@xxxxxxxxxx]

Thanks very much. Works like a charm!

Chris

Re: [eclipselink-users] Help with having clause

James Sutherland

to:

eclipselink-users

08/26/2009 06:57 AM

Sent by:

eclipselink-users-bounces@xxxxxxxxxxx

Please respond to EclipseLink User Discussions

---

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.

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