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.
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.
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.
Reads data from table ZHEADER and it connects with the CDS view ZCDS_ITEM via association.
Reads data from table ZITEM.
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.
Reads data from ZHEADER and ZITEM via CDS views and associations.
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:
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.
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. 🙂
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].ItemThe 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:
- Create an analytical model based on ABAP CDS views
- Concatenate multiple records in a single field using ABAP CDS Table Function
- Delete duplicate entries in ABAP CDS views using Table Function and SQL Window Function
- Smart charts in a SAP Fiori Object Page using ABAP CDS views and annotations
- Create KPIs with data points and criticality calculation in a SAP Fiori Overview Page
As always, hope you enjoyed the content and see you next time. 🙂
- SAP Managed Tags:
6 Comments
| User | Count |
|---|---|
| 4 | |
| 3 | |
| 3 | |
| 3 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
