I've been covering some interesting techniques with ABAP CDS development in the last years and recently I received a query about
associations.
People sometimes struggle to understand the value of associations in Core Data Services, in simple terms,
associations define the relationship between different entities, but associations also improve the reusability, data consumption and it can even support different development scopes (e.g. use of attributes, texts and hierarchies in the analytical engine).
By default, associations work as a LEFT OUTER JOIN, with this convention we know that our group of data is not affected directly by the result of other tables or views included in the selection.
Based on the image below, if we are selecting data from the view A and consuming fields from an association with the view B the system will bring all the data from the group A + the intersection of data between the group A and B.
The issue is that sometimes you may need to change this behavior and only collect data from intersection between both groups, in technical terms, you need to force an INNER JOIN. What to do in this case if you have only an association available to consume your data?
In this article, I am going to demonstrate how you can change the behavior of you ABAP CDS view and force an INNER JOIN while consuming data from an association.
Development
Notice this technique is suitable for scenarios that you are totally dependent on the association to consume your data. I am going to exemplify the technique using a simple set of custom tables and views but the important thing is to understand how you can affect the standard behavior and adapt the output to your needs.
Let's start by creating two custom tables and populating some random data on them.
Table: Header
Table: Item
Note: We need to create documents with multiple items and documents without any items in the table
ZITEM. The correlation of the data sets will change from a
LEFT OUTER JOIN to a
INNER JOIN and this way you will be able to understand the technique demonstrated in this post.
With the tables and data prepared, let's create two new ABAP CDS views and connect them through an association.
ABAP CDS view: ZCDS_HEADER
Reads data from table
ZHEADER and it connects with the CDS view
ZCDS_ITEM via association.
@AbapCatalog.sqlViewName: 'ZCDSHEADER'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Demo by Felipe Rodrigues'
define view ZCDS_HEADER
as select from zheader
association [0..*] to ZCDS_ITEM as _Item on $projection.Document = _Item.Document
{
key vbeln as Document,
_Item
}
ABAP CDS view: ZCDS_ITEM
Reads data from table
ZITEM.
@AbapCatalog.sqlViewName: 'ZCDSITEM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Demo by Felipe Rodrigues'
define view ZCDS_ITEM
as select from zitem
{
key vbeln as Document,
key vbelp as Item
}
With both CDS views created and pointing to the header and item tables we can create a new view to combine the data sets consuming data from
ZCDS_HEADER and the item number through the association with
ZCDS_ITEM.
ABAP CDS view: ZCDS_DOCUMENT_AND_ITEMS
Reads data from
ZHEADER and
ZITEM via CDS views and associations.
@AbapCatalog.sqlViewName: 'ZCDSDOCITEMS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Demo by Felipe Rodrigues'
define view ZCDS_DOCUMENT_AND_ITEMS
as select from ZCDS_HEADER
{
key Document,
key _Item.Item
}
Note #1: Pay attention to the warning message that appears over the line you exposed the association
_Item, the IDE informs the cardinality can be influenced because we have a relation of 0..N between header and items (you will understand why I mentioned this note during the course of this article).
Currently, the output of this view is based on all the entries from the header table + all the available entries in the item table, a classic scenario of
LEFT JOIN (or
LEFT OUTER JOIN).
Run the
Data Preview and check the
Number of Entries.
There is a total of 16 rows, result of the
LEFT JOIN between both tables.
Time to update the code and force the
INNER JOIN. Get back to the view
ZCDS_DOCUMENT_AND_ITEMS and update the line with the
Item field replacing it by the following code:
key _Item[inner].Item
The command
[inner] adjust the relation between both groups extracting only the results in the intersection. From now on, the view brings only the documents that have valid items.
Note #2: The warning message about cardinality is still there because of the declaration of the association
[0..*] inside of the view
ZCDS_HEADER. It is important to understand that the
INNER JOIN doesn't fix the cardinality between both views, it just adjusts the scope of data returned by the selection.
Run the
Data Preview one more time and check the
Number of Entries.
Now, there is a total of 11 rows because 5 documents don't have a valid item in the table
ZITEM and consequently they were removed from the result list.
With this demonstration we conclude our development session.
Interested in more content about ABAP CDS?
In the future, I am planning to post other simple techniques to help developers working with ABAP CDS views. If you want to learn more about ABAP CDS, associations, annotations and the usage of ABAP CDS in analytics and SAPUI5 developments I recommend to have a look in some of my previous posts:
As always, hope you enjoyed the content and see you next time.
🙂