Hibernate version: 3.2.1
Hi,
I have following SQL query set up as view:
Code:
select ACCOUNT_ASSET.* from ACCOUNT_ASSET
right join (
select ACCOUNT_ASSET.ACCOUNT_ID, max(ACCOUNT_ASSET.REPORT_DATE) as REPORT_DATE
from ACCOUNT_ASSET
group by ACCOUNT_ASSET.ACCOUNT_ID
) as LATEST_UPDATE
on ACCOUNT_ASSET.ACCOUNT_ID = LATEST_UPDATE.ACCOUNT_ID and ACCOUNT_ASSET.REPORT_DATE = LATEST_UPDATE.REPORT_DATE;
As you can see I do a self join to get the latest update on the table.
Now I need an additional parameter to get the latest update up to a specific reference date:
Code:
select ACCOUNT_ASSET.* from ACCOUNT_ASSET
right join (
select ACCOUNT_ASSET.ACCOUNT_ID, max(ACCOUNT_ASSET.REPORT_DATE) as REPORT_DATE
from ACCOUNT_ASSET
[b]where REPORT_DATE <= [color=red]?[/color][/b]
group by ACCOUNT_ASSET.ACCOUNT_ID
) as LATEST_UPDATE
on ACCOUNT_ASSET.ACCOUNT_ID = LATEST_UPDATE.ACCOUNT_ID and ACCOUNT_ASSET.REPORT_DATE = LATEST_UPDATE.REPORT_DATE;
But I already fail with converting the first SQL query into HQL:
Code:
from AccountAsset asset
right join (
select (asset2.accountId, max(asset2.reportDate) as reportDate)
from AccountAsset asset2
group by asset2.accountId) as latestUpdate
with asset.reportDate = latestUpdate.reportDate and asset.accountId = latestUpdate.accountId
It complains about the first paranthesis directly after join. So how to actually do a self join (join on the same table)?
Thanks in advance,
Joerg
Login
FAQ
Search