Using aggregate function in arithmetic expression in CDS view not supported?

Former Member — Fri, 13 Mar 2020 07:36:38 GMT

I am trying to use an aggregate function in an arithmetic expression in a CDS view, but it says that the expression is not supported at this point. Is this a limitation in CDS? It seems like this should work.

define view ZTMP_PO_ITEM as select from ekpo as p
left outer join ekko as k
    on p.ebeln = k.ebeln
left outer join lfa1 as l
    on l.lifnr = k.lifnr
left outer join ekbe as b
      on b.ebeln = p.ebeln and b.ebelp = p.ebelp and b.vgabe = '1'    
{
    p.ebeln as ebeln,
    p.ebelp as ebelp,
    p.matnr as matnr,
    p.txz01 as txz01,
    p.werks as werks,
    p.lgort as lgort,
    p.menge as menge,
    p.meins as meins,
    p.netpr as netpr,
    p.netwr as netwr,
    k.waers as waers,
    k.lifnr as lifnr,
    l.name1 as name1,
    sum(b.menge) as lmeng,
    p.menge - sum(b.menge) as umeng
}
group by p.ebeln, p.ebelp, p.matnr, p.txz01, p.werks, p.lgort, p.menge, p.meins, p.netpr, p.netwr, k.waers, k.lifnr, l.name1

Turning this into a native SELECT and passing it to the database works:

SELECT 
    P.EBELN AS EBELN,
    P.EBELP AS EBELP,
    P.MATNR AS MATNR,
    P.TXZ01 AS TXZ01,
    P.WERKS AS WERKS,
    P.LGORT AS LGORT,
    P.MENGE AS MENGE,
    P.MEINS AS MEINS,
    P.NETPR AS NETPR,
    P.NETWR AS NETWR,
    K.WAERS AS WAERS,
    K.LIFNR AS LIFNR,
    L.NAME1 AS NAME1,
    SUM(B.MENGE) AS LMENG,
    P.MENGE - SUM(B.MENGE) AS UMENG
FROM EKPO AS P
LEFT OUTER JOIN EKKO AS K
    ON P.EBELN = K.EBELN
LEFT OUTER JOIN LFA1 AS L
    ON L.LIFNR = K.LIFNR
LEFT OUTER JOIN EKBE AS B
      ON B.EBELN = P.EBELN AND B.EBELP = P.EBELP AND B.VGABE = '1'    


GROUP BY P.EBELN, P.EBELP, P.MATNR, P.TXZ01, P.WERKS, P.LGORT, P.MENGE, P.MEINS, P.NETPR, P.NETWR, K.WAERS, K.LIFNR, L.NAME1

Re: Using aggregate function in arithmetic expression in CDS view not supported?

BiberM — Fri, 13 Mar 2020 18:05:33 GMT

I think the limitations is the difference wich uses an aggregate as input (umeng).

You could stack another view on top that calculates that difference.

topic Using aggregate function in arithmetic expression in CDS view not supported? in Application Development and Automation Discussions

Using aggregate function in arithmetic expression in CDS view not supported?

Re: Using aggregate function in arithmetic expression in CDS view not supported?