Skip to Content
Technical Articles

Consume data from an ABAP CDS association via INNER JOIN

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. 🙂

5 Comments
You must be Logged on to comment or reply to a post.
  • Hi Felipe,

    Thanks for this useful info. Curious to know, if you are listing 2 different fields from same association such as

    _Item[inner].Item,

    _Item.Another_field      /*(considering left outer join)*/

    Do you know if the association will be executed twice ones each for left outer and inner join?

    Note – Currently i do not have a system to check by myself, so am asking you this query. Any input is welcomed.

    Regards,

    Deo

    • Hi Deodutt Dwivedi,

      This is an excellent question! Thank you for sharing it.

      To make things easier I included an extra field in the table ZITEM:

      • TEST (type CRMT_BOOLEAN)

      I’ve also changed the CDS views to expose the new field and I’ve updated the ZCDS_DOCUMENT_AND_ITEMS with your suggestion.

      @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[inner].Item,
          
              _Item.Test
      }

      Same no. of records in the tables ZHEADER and ZITEM but check the new result provided by the CDS view:

      You might be wondering why we have a total of 31 rows now and the answer is simple, when we include the new line to collect a field from the association _Item without the statement [inner] the compiler understands that we need to select data from ZCDS_ITEM in a separate call and it starts to increase the Cartesian Product with a new connection with the table ZITEM. Instead of processing 2 groups of data we now have 3 groups in the same selection:

      • Group A – Header
      • Group B – Item (inner join)
      • Group C – Item (left join)

      Notice that the query result still brings only the documents with a valid item but it multiplies by the no. of items in each one of them:

      • Document 0000000001
        • 3 items
        • Overall result 9 rows
      • Document 0000000006
        • 2 items
        • Overall result 4 rows

      As final proof we can compare the SQL Create Statement of both versions of the CDS view.

      Version 1: INNER JOIN only

      CREATE VIEW "ZCDSDOCITEMS" AS SELECT 
        "ZCDSHEADER"."MANDT" AS "MANDT", 
        "ZCDSHEADER"."DOCUMENT", 
        "=A0"."ITEM" 
      FROM "ZCDSHEADER" "ZCDSHEADER" INNER JOIN "ZCDSITEM" "=A0" ON ( 
        "ZCDSHEADER"."MANDT" = "=A0"."MANDT" AND 
        "ZCDSHEADER"."DOCUMENT" = "=A0"."DOCUMENT" 
      )

      Version 2: INNER JOIN and LEFT JOIN

      CREATE VIEW "ZCDSDOCITEMS" AS SELECT 
        "ZCDSHEADER"."MANDT" AS "MANDT", 
        "ZCDSHEADER"."DOCUMENT", 
        "=A0"."ITEM", 
        "=A1"."TEST" 
      FROM ( 
        "ZCDSHEADER" "ZCDSHEADER" INNER JOIN "ZCDSITEM" "=A0" ON ( 
          "ZCDSHEADER"."MANDT" = "=A0"."MANDT" AND 
          "ZCDSHEADER"."DOCUMENT" = "=A0"."DOCUMENT" 
        ) 
      ) LEFT OUTER MANY TO MANY JOIN "ZCDSITEM" "=A1" ON ( 
        "ZCDSHEADER"."MANDT" = "=A1"."MANDT" AND 
        "ZCDSHEADER"."DOCUMENT" = "=A1"."DOCUMENT" 
      )

      Hope this information helps.

      Cheers,

      Felipe Rodrigues

  • Hi Felipe, are you aware of any best practice in the usage of associations versus join? I read something about but the vision is still vague.. in general should you prefer using join versus association when there is a need to retrieve calculated KPIs while associations should be preferred when defining a semantic relationship such as item-header or document-referring document?

    What are the implications in terms of performances?

    Thanks