CDS View - delete entries with duplicate key

[Former Member]

Hello,

does someone know if there is a way to remove entries that have the same key from a CDS View? Distinct or Group By actually removes only duplicate entries, not entries that have just a duplicate key. Is there a way to use the select "DISTINCT" on only the key fields?

Thank you and best regards,

Cristina

[former_member328158]

Hello Cristina,

I have the same issue.

Can you please provide the Source code of the solution ?

[former_member228588 · Accepted solution]

I think we can use Group by and Having count clause to remove duplicate entry.

[Former Member · Accepted solution]

It worked, thank you!

[Former Member · Accepted solution]

Can you please mention how to delete duplicate records using Group by and Having count .

[周建华 · Accepted solution]

Example

Example

[former_member328158 · Accepted solution]

I had the same issue and I found a solution.

My requirement was that I had to read the Planned Goods Issue date, Actual Goods Issue date and Delivery creation date from the delivery for every Sales Order Item from the Document flow (VBFA) , but only of the first delivery Item.

Solution :

@AbapCatalog.sqlViewName: 'ZISODELDOCFLOW'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #BASIC
@EndUserText.label: 'Relation Sales Order Delivery'
define view ZI_SODELDOCFLOW as select distinct from vbfa
association [0..1] to I_DeliveryDocument as _Del on $projection.DeliveryDocument = _Del.DeliveryDocument
{
key vbfa.vbelv as SalesDoc,
key vbfa.posnv as SalesDocItem,
key vbfa.vbeln as DeliveryDocument,
min (vbfa.posnn) as DeliveryItem,
min(_Del.PlannedGoodsIssueDate) as PlannedGoodsIssueDate,
min(_Del.ActualGoodsMovementDate) as ActualGoodsIssueDate,
min(_Del.CreationDate) as DeliveryCreationDate
}
where ( vbfa.vbtyp_v = 'C' or //ORDER
vbfa.vbtyp_v = 'I' ) and //ORDER W/O CHANGE
vbfa.vbtyp_n = 'J' //DELIVERY
group by vbfa.vbelv,
vbfa.posnv,
vbfa.vbeln

[sanju_joseph]

it is a combination of min and group by clause that does the filter, for example if i don't want to have business partner repeated for different role.
then i have to use

min ( tb003t.role ) as xyz and i should not use tb003t.role in group clause ,which is also the way above code works.

[former_member328158]

I had the same issue and I found a solution.

My requirement was that I had to read the Planned Goods Issue date, Actual Goods Issue date and Delivery creation date from the delivery for every Sales Order Item from the Document flow (VBFA) , but only of the first delivery Item.

Solution :

@AbapCatalog.sqlViewName: 'ZISODELDOCFLOW'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #BASIC
@EndUserText.label: 'Relation Sales Order Delivery'
define view ZI_SODELDOCFLOW as select distinct from vbfa
association [0..1] to I_DeliveryDocument as _Del on $projection.DeliveryDocument = _Del.DeliveryDocument
{
key vbfa.vbelv as SalesDoc,
key vbfa.posnv as SalesDocItem,
key vbfa.vbeln as DeliveryDocument,
min (vbfa.posnn) as DeliveryItem,
min(_Del.PlannedGoodsIssueDate) as PlannedGoodsIssueDate,
min(_Del.ActualGoodsMovementDate) as ActualGoodsIssueDate,
min(_Del.CreationDate) as DeliveryCreationDate
}
where ( vbfa.vbtyp_v = 'C' or //ORDER
vbfa.vbtyp_v = 'I' ) and //ORDER W/O CHANGE
vbfa.vbtyp_n = 'J' //DELIVERY
group by vbfa.vbelv,
vbfa.posnv,
vbfa.vbeln