Consume data from an ABAP CDS association via INNER JOIN

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.