ABAP CDS view with GROUP BY clause returning duplicates

[alancecchini]

Hi,

I'm new to ABAP CDS views  but slowly learning by building up gradually more complex examples.

I've created the following view but it returns duplicate entries for field OPBEL, rather than one record summing the field BETRH. The only difference between the two rows returned for the Doc no. (OPBEL) = '000000510771' is field Item text (OPTXT).

I've tried various combination of key fields and have read that all fields in the SELECT list that aren't aggregated must be included in the GROUP BY clause. So, please can you advise how to best achieve this requirement?

Thanks,

Alan

@AbapCatalog.sqlViewName: 'ZV_BUPA'
@EndUserText.label: 'CDS view for business partner transactions'
define view zcdsv_bupa

as 

select from dfkkop as a


inner join but000 as b on
  a.gpart = b.partner

left outer join but021_fs as c on
  a.gpart = c.partner and c.adr_kind = 'HOME' and c.xdfadu = 'X'
left outer join adr2 as d on
  c.addrnumber = d.addrnumber

left outer join but021_fs as e on
  a.gpart = e.partner and e.adr_kind = 'TERM' and e.xdfadu = 'X'
left outer join adr2 as f on
  e.addrnumber = f.addrnumber

left outer join but021_fs as g on
  a.gpart = g.partner and g.adr_kind = 'UONEMAIL' and g.xdfadu = 'X'
left outer join adr6 as h on
  g.addrnumber = h.addrnumber and h.flgdefault = 'X'

left outer join fkkvkp as i on
  a.vkont = i.vkont

{


  a.gpart as gpart, //Business part.
  a.augst,
  case b.bpkind
    when '0003' then concat(b.name_first, b.name_last)
    else b.name_org1
  end as name, //Name
  a.vkont, //Contract acct
  a.opbel, //Doc no.
  a.bldat, //Doc date
  a.budat, //Posting date
  a.faedn, //Due date
  //a.betrh, //Amount
  sum(a.betrh ) as sum_betrh,
  a.blart, //Doc type  
  a.psrecpt, //Beneficiary
  a.optxt, //Item text
  case d.tel_number
    when '' then f.tel_number
    else d.tel_number //Tel. no.
  end as telno,
  h.smtp_addr as email,  //Email
  b.source //Industry code  

}


where a.augst = '' and a.gpart = '3000000249' and a.opbel = '000000510771'


group by a.gpart, a.augst, b.bpkind, b.name_first, b.name_last, b.name_org1, a.vkont, a.opbel, a.bldat, a.budat, a.faedn, a.blart, a.psrecpt, a.optxt, f.tel_number, d.tel_number, h.smtp_addr, b.source

[lbreddemann]

You might want to include the case-condition columns in the group by clause instead of the columns you use for the case-condition columns.

Grouping is done in reference to the output of the query (the projection list).

Also, not sure whether this is handled automatically in ABAP CDS or not, I don't see a MANDT/CLIENT field in the selection/join condition. If ABAP CDS doesn't handle this for you, this could be a reason for data duplication.

- Lars

[Former Member]

Any idea how can we put any field from projection list to the group by clause. $projection doesn't works in the groupby clause. I also had to use all the fields used in the CASE statement.

-Gaurav

[alancecchini]

Hi Gaurav,

It took me a little while to figure out what options are supported in the GROUP BY clause for ABAP-based CDS views.

Take a look at the following snippet from the SAP help documentation which clarifies the relationship between GROUP BY and aggregate expressions:

Any elements of the CDS view that which are not listed after GROUP BY must be defined in the SELECT list using aggregate expressions. Conversely, GROUP BY must be used, if aggregate expressions are contained in the SELECT list, and all elements not defined in using aggregate expressions must be listed after GROUP BY.

Regards,

Alan

[Former Member]

Thanks Alan.

     You are correct. We will need to include all the fields that are not aggregated and are present inside the select list even when in expressions.

The thing was I was wondering that is there a way where we can group by based on new fields derived from CASE or suppose any arithmetic operation (like summing to fields). It seems GROUPBY will require all the fields which are referred inside these expressions in addition to the select list. At least, I see this behavior when CASE has been used inside SELECT list.

Anyways, I will read the documentation to support my observation.

Regards,

-Gaurav