topic Re: CDS - Arithmetic operation on aggregated column in Application Development and Automation Discussions

CDS - Arithmetic operation on aggregated column

jon_humphrey — Mon, 21 Mar 2016 20:06:21 GMT

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

Re: CDS - Arithmetic operation on aggregated column

jon_humphrey — Wed, 20 Apr 2016 10:38:54 GMT

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

Many thanks,

Jon

Re: CDS - Arithmetic operation on aggregated column

Rudy_Clement1 — Tue, 26 Jul 2016 08:52:18 GMT

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.

Re: CDS - Arithmetic operation on aggregated column

jon_humphrey — Tue, 26 Jul 2016 09:19:46 GMT

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

Re: CDS - Arithmetic operation on aggregated column

kilian_kilger — Wed, 27 Jul 2016 20:08:14 GMT

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.