How to create Associations in
ABAP CDS Views
This document is step by step guide on how to create associations in ABAP CDS
views.
Prerequisites
Install ABAP Development Tools (Eclipse or HANA Studio)
Create ABAP CDS View.
Step-by-step Procedure
Before starting our development lets understand about the background of
associations and its usability.
What is an Association?
Association is a relationship between two CDS views.
We will understand this definition by looking at an example. Let's say we have 2
CDS views, one: CDS View-1 which will retrieve the order header information and
two: CDS View-2 which will retrieve the order line item information.
By establishing the relationship between these 2 CDS views on a common
field/expression to get the order header and its line item information is defined
as association.
Let's 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, let's 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 let's create an association 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
}
Association syntax on another CDS view ZCDS_SALESORDER_ITM based on the
VBELN (sales order number) field.
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
