Exploring ABAP on HANA [5] : Associations in CDS View

In this post, you will learn about Associations in CDS Views.

Read earlier posts in this series at Exploring ABAP on HANA.

What is Association?

Associations define relationships between entities such as a database table or another CDS view. Associations can also be viewed as a Join-On-Demand. This means that the association will only be executed when the fields from associated entity are referred. Typically, CDS associations can be compared to LEFT OUTER Joins.

Here is one example of a join between tables SCARR and SPFLI.

Note: All the code is available at the end in text format if you need to copy it

The same can be written using Association as below.

Explanation

• Left table is SCARR – we will call it a Primary table
• Right table is SPFLI – we will call it Associated table
• Instead of a join, association with cardinality [1..*] is used.
• Associated table is given alias starting with _ (underscore). This is not mandatory, just a naming convention.
• The ON condition uses a field from Projection list which comes from the primary table scarr.
• At the end the association i.e. _spfli is exposed.
• Selection of the key is mandatory – if key is not selected all entries from associated table are returned when association is called.

Output of the such a CDS view shows the data from LEFT table only. Data from associated table is shown only on demand.

Select a key, click on the triangle after the view name to see List of Associations and choose the one to display association data.

Notice the data returned from associated table is based on key carrid = 'AA'.

Template

Template to be used while creating association is ‘Define View with Association’.

Cardinality 

Cardinality denotes 1 entry from primary tables has a to b entries in associated table using format [a..b].

• to zero-or-one :  [ 0..1 ] or [ 1 ] or by not specifying anything
• to exactly-one :  [ 1..1 ]
• to many : [ ] or [ * ] or [ n..* ]

Cardinality	How to write	Meaning
to zero-or-one	[ 0..1 ] [ 1 ] don’t mention cardinality	One record in primary table has zero or one record in associated table
to exactly-one	[ 1..1 ]	One record in primary table has exactly one record in associated table
to many	[ ] [ * ] [ n..* ] Replace n with a number, usually 0 or 1	One record in primary table has many records in associated table [ 0..* ] means that associated table has 0 or 1 or 2 or any number of records [ 1..* ] means that associated table has 1 or 2 or any number of records but can not have 0 records

Using CDS with Associations in ABAP code

Without association fields

With association fields

• When you are accessing the association fields, use \  followed by association name and use an alias
• In the select field list use alias~fieldname
• Use the FIELDS clause to write fields after the FROM clause – which is easier to write as you have already selected an alias

Why to use associations when join can give the same data?

Think of an application that needs to show the Carriers first and when user clicks on the carrier then show the associated flights. Here, user may end up checking 4-5 carriers sometimes just 1. With joins all the data will be selected at one go. With associations only when user needs the data, it will be fetched.

Multiple associations

A CDS view can have multiple associations as below.

The data preview will show two associations in this case.

The real of associations can be seen while working with UI5 applications and OData Services.

Reference Codes:

"zjp_cds_left_join

@AbapCatalog.sqlViewName: 'ZJP_CDS_JOIN_L'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS View with Left Outer Join'
define view zjp_cds_left_join
  as select from scarr left outer join spfli
    on scarr.carrid = spfli.carrid
{ 
  key scarr.carrid      as id, 
  key scarr.carrname    as carrier, 
  key spfli.connid      as flight, 
      spfli.cityfrom    as departure,  
      spfli.cityto      as destination
} 

"zjp_cds_association_1
@AbapCatalog.sqlViewName: 'ZJP_ASSOC_1' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
@EndUserText.label: 'Simple Association' 
define view zjp_cds_association_1 as select from scarr 
association [1..*] to spfli as _spfli 
    on $projection.carrid = _spfli.carrid { 
    key scarr.carrid as carrid, 
    scarr.carrname as Carrname, 
    scarr.currcode as Currcode, 
    _spfli                        //Exposed association 
} 

"Select Queries on Association view
"Without association fields 
    SELECT carrid, carrname 
       FROM zjp_cds_association_1 
       INTO TABLE @DATA(carriers). 
    IF sy-subrc EQ 0. 
      cl_demo_output=>display( carriers ). 
    ENDIF.

"With association fields
    SELECT
       FROM zjp_cds_association_1\_spfli as flight
       FIELDS flight~carrid, flight~connid
       INTO TABLE @DATA(flights).
    IF sy-subrc EQ 0.
      cl_demo_output=>display( flights ).
    ENDIF.

"Multiple Associations
@AbapCatalog.sqlViewName: 'ZJP_ASSOC_1' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
@EndUserText.label: 'Simple Association' 
define view zjp_cds_association_1 as select from scarr 
association [1..*] to spfli as _spfli 
    on $projection.carrid = _spfli.carrid 
    association [1..*] to sflight as _sflight 
    on $projection.carrid = _sflight.carrid 
{ 
    key scarr.carrid as carrid, 
    scarr.carrname as Carrname, 
    scarr.currcode as Currcode, 
    _spfli,                        //Exposed association 
    _sflight                       //Exposed association 
}

Visit ABAP on HANA series for Tutorials on CDS, AMDP, Eclipse, and ALV IDA.

---

If you like the content, please subscribe…