Hello,

I’m using Hibernate 3, with Oracle. I’m working on a query, and wish to apply it as a filter. The actual system and tables are much more complex, so I came up with a simpler scenario to illustrate our core problem. The nature of the query should be exactly the same as this:

TABLE:
======
Let’s say I have a table empHours, with columns: empId, month, hours. It records how many hours each employee worked for each month this year. I have an object mapping to this table.

QUERY:
=======
We want to find out: “For each month, find the employee with the max #hours who worked less than 40 hours.” The 40 hours would the query pararmeter.

SQL:
===
Here’s the SQL I was able to come up with. Notice the join between e1 and e2.

SELECT e1.*
FROM empHours e1,
(SELECT month, max(hours) as maxHours
FROM empHours
WHERE hours < :hour_param
GROUP BY month) e2
WHERE e1.month = e2.month
AND e1.hours = e2.maxHours;

MY QUESTION:
============
1. Can this be translated into HQL? Notice e2 itself is not a real table, or a real object.
2. Is this something we can fit in a filter? How?

I will be happy if the sql can be somehow simplified, making it easier to fit into a filter.
(In the real world, we need to apply this condition to about 50 different tables.)


Thank you!