ABAP CDS view with GROUP BY clause returning duplicates
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?
@EndUserText.label: 'CDS view for business partner transactions'
define view zcdsv_bupa
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.
when '0003' then concat(b.name_first, b.name_last)
end as name, //Name
a.vkont, //Contract acct
a.opbel, //Doc no.
a.bldat, //Doc date
a.budat, //Posting date
a.faedn, //Due date
sum(a.betrh ) as sum_betrh,
a.blart, //Doc type
a.optxt, //Item text
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