My table/entity looks like this:
*ID* | *Description* | *Error* | *batchId*
1 | test 111 | 0 | 456
2 | test 222 | 1 | 456
3 | test 333 | 0 | 456
4 | test xxx | 1 | 458
5 | test yyy | 1 | 458
I'm trying to select results in this form:
batchId | HowManyOK | HowManyErrors
456 | 2 | 1
458 | 0 | 2
In JPQL, I tried that:
SELECT
g,
SUM( CASE WHEN g.error = false THEN 1 ELSE 0 END ) AS ok,
SUM( CASE WHEN g.error = false THEN 1 ELSE 0 END ) AS ko
FROM
GoogleMerchantLog g
GROUP BY g.batchId
But it doesn't compile! It claims an error "The encapsulated expression is not a valid expression." at the SUM.
Maybe some suggestion to solve it using Criteria API?
No solution? I finally resolved mapping a native query :((
This is the code I have just written:
@Query("SELECT new be.mteam.zoomit.persistence.vo.ZoomitValidation(Z.organization, " +
"Z.documentType, " +
"Z.language, " +
"Z.recipientProfil, " +
"Z.documentDate, " +
"sum(case when(Z.status = be.mteam.zoomit.persistence.vo.ZoomitStatus.WAITVALIDATION) then 1 else 0 end), " +
"sum(case when(Z.status = be.mteam.zoomit.persistence.vo.ZoomitStatus.WAITTOSEND " +
"or Z.status = be.mteam.zoomit.persistence.vo.ZoomitStatus.POSTED " +
"or Z.status = be.mteam.zoomit.persistence.vo.ZoomitStatus.CREATED " +
"or Z.status = be.mteam.zoomit.persistence.vo.ZoomitStatus.SENDING) then 1 else 0 end), " +
"sum(case when(Z.status = be.mteam.zoomit.persistence.vo.ZoomitStatus.SENT) then 1 else 0 end)) " +
"from Zoomit as Z " +
"where Z.organization = :organization " +
"and Z.status in (be.mteam.zoomit.persistence.vo.ZoomitStatus.WAITVALIDATION," +
"be.mteam.zoomit.persistence.vo.ZoomitStatus.WAITTOSEND," +
"be.mteam.zoomit.persistence.vo.ZoomitStatus.POSTED," +
"be.mteam.zoomit.persistence.vo.ZoomitStatus.CREATED," +
"be.mteam.zoomit.persistence.vo.ZoomitStatus.SENDING," +
"be.mteam.zoomit.persistence.vo.ZoomitStatus.SENT) " +
"group by Z.organization, Z.documentType, Z.language, Z.recipientProfil, Z.documentDate")
List<ZoomitValidation> getValidations(@Param("organization") String organization);
It was successfully executed.
Your code:
From:
SELECT
g,
SUM( CASE WHEN g.error = false THEN 1 ELSE 0 END ) AS ok,
SUM( CASE WHEN g.error = false THEN 1 ELSE 0 END ) AS ko
FROM
GoogleMerchantLog g
GROUP BY g.batchId
To:
SELECT
g.batchId,
SUM(CASE WHEN (g.error = false) THEN 1 ELSE 0 END) AS ok,
SUM(CASE WHEN (g.error = true) THEN 1 ELSE 0 END) AS ko
FROM
GoogleMerchantLog g
GROUP BY g.batchId
Maven:
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-jpa</artifactId>
<version>2.2.5.RELEASE</version>
PS : Did not find a way to use statics import to shorten the ZoomitStatus enumeration.