How to SUM a Model's attribute with Criteria's CASE WHEN JPA

Question

I'm trying to create this CriteriaQuery with a SelectCase but I'm getting an error. These are my Criteria variables:

CriteriaBuilder cb;
CriteriaQuery cq;
Root<Stransaction> transaction;
Join<Stransaction, Customer> customerJoin;
Join<Customer, Country> countryJoin;
Join<Stransaction, Supplier> supplierJoin;
Join<Stransaction, Productfamily> familyJoin;

In my model everything is joined to Transaction through ID's (Country, Supplier, Customer, Family).

After joining everything with criteria, what I want is this (I know this is a performance punch in the face, I'll fix it later. I'm just learning Criteria):

SUM(CASE WHEN COUNTRY.COUNTRYNAME = :COUNTRYNAME THEN Transaction.CFR ELSE 0)

But this line is giving me an error:

selections.add(cb.sum(cb.<Number>selectCase().when(cb.equal(countryJoin.get("countryname"), country.getCountryname()), (Numeric)transaction.get("cfr")).otherwise(0)));

Especifically here:

(Numeric)transaction.get("cfr")

Because that can't be casted to Numeric. How can I send the Transaction.cfr to the selectCase()?

This is the whole method:

private void generateSelect(FilterRegion filter) throws Exception
    {
        List<Country> countries = delegate.country().findAll();
        List<Selection<?>> selections = new LinkedList<>();

        if(filter.getContext() == FAMILY_CTX)
        {
            selections.add(familyJoin.get("prodfamname"));
            cq.groupBy(familyJoin.get("prodfamname"));
        }
        else if(filter.getContext() == CUSTOMER_CTX){
            selections.add(customerJoin.get("customername"));
            cq.groupBy(customerJoin.get("customername"));
        }
        else if(filter.getContext() == SUPPLIER_CTX){
            selections.add(supplierJoin.get("suppliername"));
            cq.groupBy(supplierJoin.get("suppliername"));
        }

        for(Country country : countries)
                selections.add(cb.sum(cb.<Number>selectCase().when(cb.equal(countryJoin.get("countryname"), country.getCountryname()), (Numeric)transaction.get("cfr")).otherwise(0)));

        cq.multiselect(selections);
    }

Answer 1

transaction.get("cfr").as(Number.class)