New to the forums, scratching my head over this query and how to create a Criteria query....
SQL:
Code:
SELECT av.*
FROM AGENCYVEHICLE av
inner join (
SELECT DISTINCT MAX(CHANGEDATE) AS last_date,
AGENCYVEHICLEID AS id
FROM AGENCYVEHICLESTATUS
GROUP BY AGENCYVEHICLEID
) M ON av.AGENCYVEHICLEID=M.id
inner join AGENCYVEHICLESTATUS avs ON av.AGENCYVEHICLEID=avs.AGENCYVEHICLEID
WHERE avs.VEHICLESTATUSLUID=2 AND avs.CHANGEDATE=M.last_date
Where I'm stuck is how to associate the inner subquery with the outer criteria query. The subquery is simple:
Code:
DetachedCriteria maxDate = DetachedCriteria.forClass(VehicleStatus.class);
ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.max("changeDate"));
projectionList.add(Projections.groupProperty("vehicle"));
maxDate.setProjection(projectionList);
But how do I link this subquery to the original criteria? I've tried variations on the following:
Code:
criteria.add(Subqueries.propertyEq("statuses", maxDate));
but this generates the wrong sql.
Any suggestions, or any reasonable link to Criteria query resources? I've done two days of googling and searching here, and I'm not finding much that helps.
Thanks,
Paul Cooper
EMS Performance Center
Chapel Hill, NC
Code:
Login
FAQ
Search