Spring JPA Query - Replace negative values in result set

Question

I'm developing a Spring Boot application with Spring Data JPA. I'm using a custom JPQL query to get the data I need.

The following is my method repository:

/**
  * SELECT sku, SUM(real_allocation - logical_allocation)
  * FROM stock
  * WHERE warehouse_brand = '?'
  * GROUP BY (warehouse_brand, sku);
  */

@Query(value = "SELECT new it.reply.retail.oms.common.api.inventory.CumulativeInventoryItem " +
               "(s.pk.sku, SUM(s.realAllocation - s.logicalAllocation)) " +
               "FROM Stock s WHERE s.pk.whsCode IN :warehouses GROUP BY (s.pk.sku)")
Page<CumulativeInventoryItem> getCumulativeInventory(List<String> warehouses, Pageable pageable);

and the CumulativeInventoryItem custom object is the following one:

public class CumulativeInventoryItem {

    private String sku;
    private Long cumulativeQty;

}

The result set I obtain, sometimes, contains negative values for the cumulativeQty attribute which need to be replaced with 0 instead.

Is it possible to replace these values using ad-hoc query or I need to replace them once I get the result set back from my JPA method?

wia wia · Accepted Answer · 2021-01-09T15:12:48

According to this: https://en.wikibooks.org/wiki/Java_Persistence/JPQL_BNF#New_in_JPA_2.0

the CASE WHEN THEN ELSE combination can be use there.

May can you try

SUM(CASE WHEN (s.realAllocation - s.logicalAllocation < 0) THEN 0 ELSE (s.realAllocation - s.logicalAllocation)))

instead of

SUM(s.realAllocation - s.logicalAllocation)

Spring JPA Query - Replace negative values in result set

1 Answers