CASE statement in HQL or criteria

Question

derived from this question , is it possible to use HQL or criteria for the following SQL statement:

SELECT e.type, count(e), count(d), count (case when gender = 'male' then 1 else NULL end) AS NumberOfMaleEmployees from Department d JOIN d.employees e WHERE e.dead = 'maybe' GROUP BY e.type 

Although google offers several hits that claim that HQL supports CASE statements, Hibernate 3.6.6 crashes with

QuerySyntaxException: unexpected token: CASE

when I create the request above in an EntityManager instance.

How bad idea, create another request for each e.type to determine the number of men manually, for example. for each e.type

 SELECT count(e), from Department d JOIN d.employees e WHERE e.dead = 'maybe', e.type = ugly 

Since there can be quite a few types, this is potentially slow. I would like the database to do my work.

Answer 1

Well, it seems case arguments are supported:

http://docs.jboss.org/hibernate/core/3.5/reference/en/html/queryhql.html

They just don't work in count (). An alternative would be to use sum (case when ... then 1 else 0 end) instead