Hibernate version: 3.2.2.ga

Is it possible to create a Criteria for the following SQL statement:

select * from mt_invoice_imports mt1,
(select file_name, account_id, max(upload_date) as upload_date
from mt_invoice_imports
where to_char(upload_date, 'yyyy') like '%2007%' and account_id = 63453
group by file_name, account_id) mt2
where mt1.file_name = mt2.file_name and mt1.account_id = mt2.account_id and mt1.upload_date = mt2.upload_date

I was able to create a Criteria for the subquery:

DetachedCriteria subquery = DetachedCriteria.forClass(InvoiceImportEntity.class);
ProjectionList projectionList = Projections.projectionList();

projectionList.add(Projections.max("uploadDate").as("uploadDate"));
projectionList.add(Projections.groupProperty("fileName"));
projectionList.add(Projections.groupProperty("accountEntity"));

subquery.add(Restrictions.sqlRestriction("to_char(UPLOAD_DATE, 'yyyy') LIKE '%" + year + "%'"));
subquery.add(Restrictions.eq("accountEntity", accountEntity));
subquery.setProjection(projectionList);

However, I'm having problems creating a Criteria for the rest of the SQL--particularly, how to create an inner join with a subquery.

The subq in The criteria API are meant to be used in the where clause, not in the select, I think.
Also make sure to take a look at this if you have joins within you subq

http://opensource.atlassian.com/project ... se/HHH-952

An issue that has a patch but was never pushed to the hib code.


** Rate if this helps