Skip to Content
0

CDS View - delete entries with duplicate key

Jan 16, 2017 at 12:34 PM

1.2k

avatar image
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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
avatar image
Former Member Jan 16, 2017 at 08:27 PM
1

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

Show 4 Share
10 |10000 characters needed characters left characters exceeded
Former Member

It worked, thank you!

0
Former Member

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

0
Former Member

Example

Example

0
Former Member

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

0
avatar image
Former Member Sep 12, 2017 at 02:15 PM
0

Hello Cristina,

I have the same issue.

Can you please provide the Source code of the solution ?

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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

1