jpajpqlcriteria-apicase-when

"CASE WHEN" clause inside SUM


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 :((


Solution

  • 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.