Skip to Content
0
Feb 03, 2023 at 12:21 AM

SAP CAP Java - CQL SELECT with Having and group by clause

518 Views Last edit Feb 03, 2023 at 12:26 AM 3 rev

Hi Team,

We have a view that we need to query.

SELECT SUM(CARDCOUNT) as CardTotal, x
    FROM CARDUSAGEVIEW 
    WHERE TRANSACTIONDATE >= '2023-01-01' AND TRANSACTIONDATE <= '2023-02-02'
    GROUP BY x
    HAVING (SUM(CARDCOUNT) >=12)

I have converted to CqnSelect and this works fine.

 CqnSelect cqnFilter = Select.from(CardUsageView_.CDS_NAME)
                     .columns(u -> sum(get("cardCount")).as("cardTotal"), 
                              u -> u.get("x"))
                      .groupBy(u -> u.get("x"))
                      .having(u -> sum(get("cardCount")).ge(12))
                      .where(whereclause);

We need to be able to use the filter entered in the filterbar by user. Eg Greater or Equal to "12".

We want the having clause to use these in the sum(x) "OPERATOR FROM Filter" 12. Having trouble working out how to code the "having" clause. We don't want to hard code the operator in the query.

Any help much appreciated.

Cheers

Dharmesh