Hello everyone, in this blog post you will learn about Associations in ABAP CDS views and a step by step guide on how to create associations in ABAP CDS views so that you gain knowledge and start implementing them in your projects very easily.
If you are an experienced ABAP developer and have basic knowledge on ABAP CDS view you may jump directly to the step-by-step guide.
For beginners you may have to start with prerequisites section and come back to this blog post to further gain knowledge on ABAP CDS views. Lets get started
Prerequisites
- Install ABAP Development Tools in Eclipse.
- Create your first ABAP CDS View.
- and some other ABAP CDS Views knowledge gainer blog.
Step-by-step Procedure
Before starting our development lets understand about the background of associations and its usability.
What is an CDS View Association?
Association is a relationship between two CDS views. We will understand this definition by looking at an example.
Lets say we have 2 CDS views, one CDS View1 which will retrieve the order header information and a second CDS View2 which will retrieve the order line item information.
CDS View Association is nothing but, establishing the relationship between these 2 CDS views on a common field/expression to get the order header and its line item information.
Lets start our development to understand even better
1. Create a CDS View to get the sales order header information from the database table(VBAK). Below is the sample DDL code snippet for the same.
@AbapCatalog.sqlViewName: 'ZV_ORD_HDR'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Order Header Information'
define view ZCDS_SALESORDER_HDR
as select from vbak
{
vbeln,
erdat,
vbtyp,
auart,
vkorg,
vtweg,
spart,
netwr,
waerk
}
2. Create another CDS view to get the sales order item information from the database table(VBAP). Below is the sample DDL code snippet for the same.
@AbapCatalog.sqlViewName: 'ZV_ORD_ITM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Order Item Information'
define view ZCDS_SALESORDER_ITM
as select from vbap
{
vbeln,
posnr,
matnr,
zwert,
zmeng
}
the above CDS view seems to simple select from single table, lets make it complex by adding JOINS to get more details.
@AbapCatalog.sqlViewName: 'ZV_ORD_ITM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Order Item Information'
define view ZCDS_SALESORDER_ITM
as select from vbap
left outer join makt on vbap.matnr = makt.matnr
{
vbeln,
posnr,
vbap.matnr,
makt.maktx,
zwert,
zmeng
}
Association on ABAP CDS Views
Now lets create an associations between these two CDS views. Below is the syntax for creating associations.
@AbapCatalog.sqlViewName: 'ZV_ORD_HDR'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Order Header Information'
define view ZCDS_SALESORDER_HDR
as select from vbak
association to ZCDS_SALESORDER_ITM as _OrderItems
on vbak.vbeln = _OrderItems.vbeln
{
key vbeln,
erdat,
vbtyp,
auart,
vkorg,
vtweg,
spart,
netwr,
waerk,
_OrderItems.matnr,
_OrderItems.maktx
}
Line 7-8: association syntax on another CDS view ZCDS_SALESORDER_ITM based on the VBELN(sales order number) field.
Line 19-20: fields from the association can be accessed in the SELECT list by prefixing them with association name followed by period( . ) _OrderItems.matnr
Association with cardinality
Now lets try to add the cardinality on the target cds view which is defined with association with this syntax [min..max]
@AbapCatalog.sqlViewName: 'ZV_ORD_HDR'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Order Header Information'
define view ZCDS_SALESORDER_HDR
as select from vbak
association [1..*] to ZCDS_SALESORDER_ITM as _OrderItems
on vbak.vbeln = _OrderItems.vbeln
{
key vbeln,
erdat,
vbtyp,
auart,
vkorg,
vtweg,
spart,
netwr,
waerk,
_OrderItems.matnr,
_OrderItems.maktx
}
Below are the rules for min and max values.
- max cannot be 0.
- An asterisk * for max means any number of rows.
- min can be omitted (set to 0 if omitted).
- min cannot be *.
- When an association is used in a WHERE condition, 1 must be specified for max.
Association ON condition rules
When specifying the ON condition with association, following rules are applied.
- The fields specified in the ON condition should be included in the SELECT list. In our example, the field VBELN is specified in ON condition and SELECT list.
- The fields of source data source can be prefixed with $projection instead of data source name.
define view ZCDS_SALESORDER_HDR as select from vbak association [1..*] to ZCDS_SALESORDER_ITM as _OrderItems on $projection.vbeln = _OrderItems.vbeln { ..... } - the fields of target data source should be prefixed with association name. _OrderItems.vbeln
Data Preview
Right click on the DDL editor
Output data of the ABAP cds view will be displayed like below
Choose any one record and right click on it and choose Follow Association
List of associations will be displayed in a window like below, choose the relevant association
Double click on the association to see the data
Association as join type
The association defined in an ABAP CDS view will be converted to join type at run-time. By default the join type is LEFT OUTER JOIN.
The below CDS view will result in LEFT OUTER JOIN when executed.
@AbapCatalog.sqlViewName: 'ZV_ORD_HDR'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Order Header Information'
define view ZCDS_SALESORDER_HDR
as select from vbak
association [1..*] to ZCDS_SALESORDER_ITM as _OrderItems
on $projection.vbeln = _OrderItems.vbeln
{
key vbeln,
erdat,
vbtyp,
auart,
netwr,
waerk,
_OrderItems.matnr
}
To achieve INNER JOIN, you need to define the attribute in the path expression like below.
@AbapCatalog.sqlViewName: 'ZV_ORD_HDR'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Order Header Information'
define view ZCDS_SALESORDER_HDR
as select from vbak
association [1..*] to ZCDS_SALESORDER_ITM as _OrderItems
on $projection.vbeln = _OrderItems.vbeln
{
key vbeln,
erdat,
vbtyp,
auart,
netwr,
waerk,
_OrderItems[inner].matnr
}
Usage of CDS view with association in ABAP using OpenSQL
We can access the CDS view which has associations defined in ABAP program using Open SQL statement using the path expressions like below
DATA(is_supported) = cl_abap_dbfeatures=>use_features(
requested_features = VALUE #( ( cl_abap_dbfeatures=>views_with_parameters ) ) ).
IF is_supported IS NOT INITIAL.
SELECT *
FROM zcds_salesorder_hdr
INTO TABLE @DATA(lt_data).
ENDIF.Want to learn about new ABAP syntax, click here
DATA(is_supported) = cl_abap_dbfeatures=>use_features(
requested_features = VALUE #( ( cl_abap_dbfeatures=>views_with_parameters ) ) ).
IF is_supported IS NOT INITIAL.
SELECT vbeln,
erdat,
\_orderitems-matnr AS material
FROM zcds_salesorder_hdr
INTO TABLE @DATA(lt_data).
ENDIF.Congrats.! You have successfully learned on how to create associations in ABAP CDS views.Please stay tuned to us for more ABAP CDS view tutorials.
