CDS - Arithmetic operation on aggregated column

[jon_humphrey]

Hi all,

I am trying to figure out the syntax (or even if it is permitted) to perform an arithmetic operation on an aggregated column.

for example - we can create

define view Zpo as

select from ekko

    join ekpo on ekko.ebeln = ekpo.ebeln

    join eket on ekpo.ebeln = eket.ebeln

    and ekpo.ebelp = eket.ebelp

    join mara on ekpo.matnr = mara.matnr

    join makt on mara.matnr = makt.matnr{

    ekko.ebeln,

    ekpo.ebelp,

    bsart,

    ekko.bukrs,

    ekko.statu,

    zterm,

    ekpo.matnr,

    maktx,

    werks,

    lgort,

    mara.matkl,

    eindt,

       ekpo.netpr,

    sum(eket.menge) as TOT_MENGE

        }

    where makt.spras = 'E'

    group by

    ekko.ebeln,

    ekpo.ebelp,

    bsart,

    ekko.bukrs,

    ekko.statu,

    zterm,

    ekpo.matnr,

    maktx,

    werks,

    lgort,

    mara.matkl,

    eindt,

    ekpo.netpr   

and we can create

define view Zpo as

select from ekko

    join ekpo on ekko.ebeln = ekpo.ebeln

    join eket on ekpo.ebeln = eket.ebeln

    and ekpo.ebelp = eket.ebelp

    join mara on ekpo.matnr = mara.matnr

    join makt on mara.matnr = makt.matnr{

    ekko.ebeln,

    ekpo.ebelp,

    bsart,

    ekko.bukrs,

    ekko.statu,

    zterm,

    ekpo.matnr,

    maktx,

    werks,

    lgort,

    mara.matkl,

    eindt,

       ekpo.netpr,

   eket.menge * 10 as TOT_MENGE10

        }

    where makt.spras = 'E'

    group by

    ekko.ebeln,

    ekpo.ebelp,

    bsart,

    ekko.bukrs,

    ekko.statu,

    zterm,

    ekpo.matnr,

    maktx,

    werks,

    lgort,

    mara.matkl,

    eindt,

    ekpo.netpr ,

  eket.menge

but how to perform an calculate that only performs the sum of the calculation without having to include the aggreated field in the group by clause.

Something like:

define view Zpo as

select from ekko

    join ekpo on ekko.ebeln = ekpo.ebeln

    join eket on ekpo.ebeln = eket.ebeln

    and ekpo.ebelp = eket.ebelp

    join mara on ekpo.matnr = mara.matnr

    join makt on mara.matnr = makt.matnr{

    ekko.ebeln,

    ekpo.ebelp,

    bsart,

    ekko.bukrs,

    ekko.statu,

    zterm,

    ekpo.matnr,

    maktx,

    werks,

    lgort,

    mara.matkl,

    eindt,

       ekpo.netpr,

    sum(eket.menge) as TOT_MENGE,

    sum(eket.menge * 10) as totmenge10 //or sum(eket.menge) * 10 as totmenge10

        }

    where makt.spras = 'E'

    group by

    ekko.ebeln,

    ekpo.ebelp,

    bsart,

    ekko.bukrs,

    ekko.statu,

    zterm,

    ekpo.matnr,

    maktx,

    werks,

    lgort,

    mara.matkl,

    eindt,

    ekpo.netpr   

I get various errors ranging from 'Syntax Errors) to This function or expression ('sum') is not supported at this point.

database HANA  sql has not problem with this - random example below:

SUM((A.UNIT_SELLINGPRICE - a.unit_discount) * a.quantity) AS NET_PRICE_INC_TAX

Thanks,

Jon

[jon_humphrey]

Hi, , , I wondered if you had any suggestions on this?

Many thanks,

Jon

[Rudy_Clement1]

Hi Jon,

Any luck with this already? I'm also trying to cumulate the sum of multiple columns (unrestricted stock, stock in transfer and stock in quality inspection) into one column (total stock).

In OpenSQL has no problems with this:

SELECT
         mara~matnr,
         mara~meins,
         SUM( mard~labst + mard~umlme + mard~insme ),
         SUM( mard~labst ),
         SUM( mard~umlme ),
         SUM( mard~insme )
       FROM mara AS mara
       INNER JOIN marc AS marc
         ON marc~matnr EQ mara~matnr
       LEFT OUTER JOIN mard AS mard
         ON mard~matnr EQ mara~matnr
         AND mard~werks EQ marc~werks
       INTO TABLE @et_entityset
       WHERE mara~matnr IN @zlt_r_matnr
         AND ( mard~labst GT 0 OR mard~umlme GT 0 OR mard~insme GT 0 )
       GROUP BY mara~matnr, mara~meins
ORDER BY mara~matnr.

And in HANA views I've also done this many times. But in CDS it seems impossible to do it within 1 single view. Or at least i can't figure out how...

Kind regards,

Rudy.

[jon_humphrey]

Hi Rudy,

No - I never got an answer and assumed it was just syntactically not possible.  A shame as it seems overly long-winded to have to have a 'calculation ' CDS view and then an 'aggregation' CDS view on top of it (although I can equally see that having the split might sometime be beneficial).

If you have any luck then let me know.

Thanks,

Jon

[kilian_kilger]

Hi,

for CDS the usual technique is to do "view on view", as was already mentioned. My collegues always try to improve the "expression matrix", as they call it. But it will take some time to get it to the full extend.

We in Open SQL implemented no expression matrix. So you can arbitrarily nest all (most) expressions. The only limitation I know of is that you can't do aggregations inside expressions, i.e. 1 + sum( ... ) will not work in Open SQL. This is because the databases have different overflow behaviour for the aggregates and probably want a common overflow semantics.

Best regards,
Kilian.