I'm writing a simple query in my java code using eclipselink v2.3. This query must simply return a String and two integers, nothing strange I think, or at least I thought,
The query I'm building is the following:
q = entityManager.createQuery(
"SELECT new com.myclass.CalculationQueryResult(transits.device.name,"
+ " SUM(case when transits.direction = 1 then 1 else 0 end) ,"
+ " SUM(case when transits.direction = 0 then 1 else 0 end)) from Transits_Log transits "
+ " where transits.device.name in :devices and transits.dateTime >= :startDate"
+ " and transits.dateTime < :endDate group by transits.device.name" + " order by transits.device.name",
CalculationQueryResult.class);
While it, obviuosly works in SQL Server (our native counterpart), this does not work in JPQL.
The two different (SUM -> CASE) clauses were strangely (at least for me that i'm quite new to JPA) equals to each other. So, I decided to take out the native SQL from the JPQL to investigate deeper and the problem was there. The generated SQL is this one:
SELECT t0.Name,
**SUM(CASE WHEN (t1.Direction = 1) THEN 1 ELSE 0 END)** ,
**SUM(CASE WHEN (t1.Direction = 1) THEN 1 ELSE 0 END)** FROM dbo.ZZZ t0,
YYYY t1
WHERE ((((t1.DeviceName IN ('XXXXX'))
AND (t1.DateTime >= {ts '2012-09-24 17:26:48.031'}))
AND (t1.DateTime < {ts '2012-09-24 18:26:48.031'}))
AND (t0.Name = t1.DeviceName)) GROUP BY t0.Name
ORDER BY t0.Name ASC
As you can see, the SQL generated statement are wrong on the first two lines 'cause the first SUM and the second one should be one the opposite of the other while they're not.
Am I doing something extremely wrong? Does JPQL support multiple nested CASE and SUM? Are there any way to circumnavigate the error(if is the case) without having to write directly native SQL code?
