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