CDS view giving duplicate entries

[former_member606508]

Hi,

I have this CDS view joining multiple tables. Issue is im getting so many duplicate entries in the output. I have gone through many blogs wherein i found few workarounds (like using group by with all the fields etc) but im wondering why im getting these duplicates. Is there any thing i can do without writing any additional code to void these duplicates.

define view ZSD_0R6_ShipmentRepor_C 
as select from likp
    inner join lips on likp.vbeln = lips.vbeln
    inner join vbak on lips.vgbel = vbak.vbeln
    inner join vbkd on vbak.vbeln = vbkd.vbeln
    inner join vbpa on lips.vbeln = vbpa.vbeln
    inner join vbep on vbak.vbeln = vbep.vbeln
    inner join kna1 on likp.kunnr = kna1.kunnr
    inner join makt on lips.matnr = makt.matnr
    
     {
     key likp.vbeln,
     key lips.posnr,
         lips.werks,
         likp.kunnr,
         kna1.name1 as Carrier_Name,
         lips.prctr,
         lips.matnr,
         makt.maktx,
         lips.lfimg,
         lips.ntgew,
         lips.gewei,
         vbep.edatu,
         vbak.vdatu,
         vbkd.bstdk,
         dats_days_between(vbkd.bstdk,vbak.vdatu) as latedays,
         vbpa.kunnr as Carrier
         }
         where vbpa.parvw = 'ZF'

Thank you

[former_member606508]

nope ! getting same duplicates.

[DoanManhQuynh]

duplicate how? you select quite lot of table, records could be repeat as the relationship from those could be 1..n, n..n etc.. CDS is just a view so it will pull all the date satisfy the condition. The KEY keyword is not same as KEY of database table.

[UweFetzer_se38]

Depends on what you are expecting. I think you want to get a record for each delivery item? Then try to use LEFT OUTER JOIN for all tables but LIPS.

[former_member284740]

Try using POSNR in your JOIN on VBKD, VBEP and VBPA.

[Guru_Charan]

Hi,

did you get the answer for the issue as Iam getting the same error?

with parameters

P_LIFNR1 : z007_lifnr

as

select from z007_tab2 as SO_J

left outer join z007_tab3 as SO_I

on SO_J.lifnr = SO_I.lifnr

right outer join z007_tab1 as SO_H

on SO_H.lifnr = SO_J.lifnr

{

key SO_J.lifnr,

key SO_I.ebeln as SO_EBELN,

key SO_I.ebelp as SO_EBELP,

SO_H.ekorg,

SO_H.ekgrp,

SO_H.bukrs,

SO_H.matnr,

SO_I.aedat,

SO_H.aenam,

SO_H.werks,

SO_H.lgort,

SO_I.menge,

SO_I.meins,

SO_I.netpr,

SO_I.waers,

SO_I.lfimg,

SO_I.lfdat,

SO_H.maktx

}

where

SO_H.lifnr = $parameters.P_LIFNR1

and SO_I.menge > SO_I.lfimg;
while writing this cds view?

thank you,