Skip to Content
0
Mar 21, 2016 at 08:06 PM

CDS - Arithmetic operation on aggregated column

4329 Views

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