Hello,

I am using Hibernate 3.0 with an Oracle 10i database. For performance reasons I have to move what is currenlty being done via an HQL query to use the Criteria interface.

This is because I've found that the createQuery interface ignores the max_fetch_depth parameter, and therefore after I retreive my list of objects, as I am iterating through them and want to get information from various other table (sometimes 3 degrees away) it begins to execute a large number of queries. In a test environment I can retrieve a list of these object using a Criteria and retreive all of the information that I need without executing additional queries once the printing out of the data has started.

The issue that I'm running into is that some of the information in the tables is effective dated. The effective date that I want to use can be different for each object; therefore I cannot use a filter which takes in a single effective date for the entire search.

Following is the query which works in HQL:

select distinct statement
from Statement as statement
join statement.customer as customer
join statement.customer.paymentMethods as paymentMethod
left outer join statement.paymentTransactions as paymentTransaction
and (statement.status in ('PAID')
and (paymentTransaction.creationDate =
(select max(pt.creationDate)
from paymentTransaction as pt
where pt.statement.id = statement.id)
and paymentMethod.class is CreditCardPaymentMethod
and paymentMethod.effectiveDate <= paymentTransaction.creationDate
and paymentMethod.effectiveDate >= all
(select pm.effectiveDate
from com.kuvata.objects.PaymentMethod as pm
where pm.customer.id = customer.id
and pm.effectiveDate <= paymentTransaction.creationDate))


To summarize, I want to get a list of all PAID statements that had a Credit Card payment method at the time that the most recent transaction posted against that statement (which would be the one that made it go PAID).

The specific thing that I am having trouble doing with revolves around the 'pt.statement.id = statement.id' and the 'pm.effectiveDate <= paymentTransaction.creationDate' clauses, where the subquery is refering to something from the greater statement.

Does anybody have any suggestions on how this could be done using a Criteria, or perhaps insight into another strategy that I am not seeing yet?

Thanks,

Jeff

Oops, the syntax of that statement is incorrect. I took out a couple of irrelevelent clauses in an effort to simplify it. It is missing the first where, but you get the idea.