Skip to Content

CDS - Arithmetic operation on aggregated column

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Jul 26, 2016 at 08:52 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • Posted on Apr 20, 2016 at 10:38 AM

    Hi, @Jasmin Gruschke , @Jens Weiler, I wondered if you had any suggestions on this?

    Many thanks,


    Jon

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.