How to create Associations in ABAP CDS Views

0
1413

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

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. Lets 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 command field/expression to get the order header and its line item information is defined as association. 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.
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.

 

Disadvantages

Following this approach of creating OData service for ABAP CDS Views using the annotation @OData.publish : true, you will have one service with only one entity. If your requirement is to have multiple entities in a single OData service this may not a better approach to fulfill your requirement. An alternative approach, by referring the ABAP CDS view as a data model in SEGW is available and a recommended approach in most of the use cases. You can find this approach here.

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.