UNION with Aggregation in CDS View

[former_member281438]

Hello Experts,

                   I created cds view with union of two tables. Here my problem is i am able to aggregate the data(sum) for individual selects but not for entire CDS view.

                   Is it possible to aggregate the data after union in CDS view? Please help me.

  here is my code.

  define view zv_cds as

select from tab1

{

  key,

sum(value) as value,

cast(0 as abap.curr(15, 2) ) as value2

} group by key

union

select from tab2

{

key,

cast(0 as abap.curr(15, 2) ) as value,

sum(value2) as value2

} group by key

in above example, i used aggregation on individual select query but i want to use aggregation on entire CDS View.

Thanks,

Venkatesh

[Former Member]

Venkatesh,

I think the most easy solution for this is writing a second view that calls you zv_cds view, selects the fields of your view and also uses a group by on the key.

Then you get something like this:

define view zv_cds2 as

select from zv_cds

{

  key,

  sum(value) as value,

  sum(value2) as value2

} group by key

[former_member281438]

Thanks for your response.

       You are correct but my requirement is to achieve in one CDS View. I achieved it by union of left outer join of table1 and table 2 and unmatched rows from table 2.

Thanks,

Venkatesh