Hi

I'm trying to achieve the following

select acc.*,
(case when balance >= 2*cmp then 'TRUE' else 'FALSE' end)
from account acc
where regulated_by = 'ABC'
and redemption_date is null
and arrears_balance > cmp

The case statement is the bit I'm having problems with. I thought discriminators might be the way to do it (because of their 'formula' value) but now I'm not sure.

Could anyone give me a hint as to what is the best approach please?

Thanks

I think this has the Hibernate Criteria API all over it. You can use the Criteria API to create very complicated queries based on your objects, not tables. Remember, Hibernate is all about thinking about your problem domain in the form of your object model, not the underying tables.

Here's a fun little tutorial on the Hibernate3 Criteria API.

http://jpa.ezhibernate.com/Javacode/learn.jsp?tutorial=09howtousethecriteriaapi

Give it a read, and tell us what your objects are that you are using. Then maybe we can help you do a Criteria query with these objects.

Thanks for the tips Cameron. Just seen your message.

I'll give that tutorial and the Criteria API a whirl - thanks again.

Always happy to help!