Duplicated records in custom CDS view in S4 hana cloud
In one of customer support case , custom create an custom cds view . They set delivery item cds as Primary Datasource , cds billing item as Associated Datasource with zero or one relation base on DN number and DN item . But in preview, delivery item duplicated which is not the expected result from customer .
The key is the understanding for Cardinality , customer think billing item has been set as 0::1 relation, so billing item always populate 0 or 1 record for one delivery item even though multi billing items exist for one delivery item(like dropship sales) . But the actual situation is relation between delivery item and billing item will be translated into 0::n relation in SQL select .
The following are the testing result in one SAP internal system . I will take sales order and sales order item as data source . There relation is 1 to N . But I will set the cardinality as 0::1 .
Since the actual relation is 1 to n , system will give a alert when checking .
Sale order record duplicated in preview .
Hope this will help in understanding view relation in custom cds view .