This is a test message to test the length of the message box.
Login
ABAP CDS Association
Created by Software-Heroes

CDS - Association

384

This article is about associations, which provide functionality similar to joins. But we will show you what the differences are.

In the last article we explained the joins and the union and how you can use them to link data in Core Data Services. You can then use these new views again and again and only have to implement the logic once. In addition to the joins, there is another way to make dependent data available in the data model.

 

Association

An association creates a relationship to dependent data and defines a connection in the fields of the CDS View known as an exposure. Without the specification in the projection list, the data cannot be accessed. To do this, we define an association in our interface view "ZBS_I_DmoInvoice". There are two possible connections in this view, one to the partner and one to the positions. In the following example we create an association to the partner:

define view ZBS_I_DmoInvoice
  as select from zbs_dmo_invoice as Invoice
  association [0..1] to ZBS_I_DmoPartner as _Partner on $projection.PartnerNumber = _Partner.PartnerNumber
{
  key document as DocumentNumber,
      doc_date as DocumentDate,
      doc_time as DocumentTime,
      partner  as PartnerNumber,
      _Partner
}

 

The association is defined after the SELECT and, like the join, is given an alias and an on condition to link the data to the target entity. In addition, the association will be exposed in the projection list of fields by specifying the name of the association. As a best practice, associations begin with an underscore to delineate the data and fields from the Core Data Service. The cardinality of the connection is stored behind the association, more on that in a later section. How can you now access this data in a select, see the following example:

SELECT FROM ZBS_I_DmoInvoice
  FIELDS DocumentNumber, PartnerNumber, \_Partner-PartnerName, \_Partner-City
  INTO TABLE @DATA(lt_association)
  UP TO 10 ROWS.

 

With the select, you can now access the fields via the defined association and mix the relevant data into the select without having to define a join. Before the underscore, however, you have to work with a backslash. The result of the query now looks like this:

 

Cardinality

The defined cardinality in the association primarily gives the database optimizer an indication of how the data should be accessed and which type of access is best. At the same time, the cardinality determines the result set. The following carinalities are possible:

  • [1] - Number 0 to 1
  • [0..1] - Number 0 to 1
  • [1..1] - Number exact 1
  • [0..*] - Number 0 to unlimited
  • [1..*] - Number 1 to unlimited
  • No information - Number 0 to 1

 

Performance

You're probably wondering what the association actually brings you? At first they behave like joins, but the secret happens with "non-access", because then it does not cost the database any performance. In contrast to a join, where the access and the data linking happens every time, the association is only triggered when fields are also addressed. Let's take a look at the SQL Create Statement, which you can access by right-clicking on the source code and selecting "Show SQL Create Statement":

 

In the next step we define the fields in the current field list and extend it with fields from the defined association:

define view ZBS_I_DmoInvoice
  as select from zbs_dmo_invoice as Invoice
  association [0..1] to ZBS_I_DmoPartner as _Partner on $projection.PartnerNumber = _Partner.PartnerNumber
{
  key document as DocumentNumber,
      doc_date as DocumentDate,
      doc_time as DocumentTime,
      partner  as PartnerNumber,
      _Partner.PartnerName,
      _Partner.City
}

 

The fields are now permanently integrated in the views, the corresponding create statement now looks like this. The same also happens when accessing the data, whether with or without the appropriate fields of the association:

 

Integration

Now we equip all interface views of the data model with associations to the linked data to enable navigation in the data model and to easily provide further data.

 

Invoice
define view ZBS_I_DmoInvoice
  as select from zbs_dmo_invoice as Invoice
  association [0..*] to ZBS_I_DmoPosition as _Position on $projection.DocumentNumber = _Position.DocumentNumber
  association [0..1] to ZBS_I_DmoPartner  as _Partner  on $projection.PartnerNumber = _Partner.PartnerNumber
{
  key document as DocumentNumber,
      doc_date as DocumentDate,
      doc_time as DocumentTime,
      partner  as PartnerNumber,
      _Position,
      _Partner
}

 

Position
define view ZBS_I_DmoPosition
  as select from zbs_dmo_position
  association [0..1] to ZBS_I_DmoInvoice  as _Invoice  on $projection.DocumentNumber = _Invoice.DocumentNumber
  association [0..1] to ZBS_I_DmoMaterial as _Material on $projection.MaterialNumber = _Material.MaterialNumber
{
  key document   as DocumentNumber,
  key pos_number as PositionNumber,
      material   as MaterialNumber,
      quantity   as PositionQuantity,
      price      as PositionPrice,
      currency   as PositionCurrency,
      _Invoice,
      _Material
}

 

Partner
define view ZBS_I_DmoPartner
  as select from zbs_dmo_partner
  association [0..1] to I_Country  as _Country  on $projection.Country = _Country.Country
  association [0..1] to I_Currency as _Currency on $projection.PaymentCurrency = _Currency.Currency
{
  key partner          as PartnerNumber,
      name             as PartnerName,
      street           as Street,
      city             as City,
      country          as Country,
      payment_currency as PaymentCurrency,
      _Country,
      _Currency
}

 

Material
define view ZBS_I_DmoMaterial
  as select from zbs_dmo_material
  association [0..1] to I_Currency      as _Currency on $projection.Currency = _Currency.Currency
  association [0..1] to I_UnitOfMeasure as _Unit     on $projection.StockUnit = _Unit.UnitOfMeasure
{
  key material       as MaterialNumber,
      name           as MaterialName,
      description    as MaterialDescription,
      stock          as Stock,
      stock_unit     as StockUnit,
      price_per_unit as PricePerUnit,
      currency       as Currency,
      _Currency,
      _Unit
}

 

Discount
define view ZBS_I_DmoDiscount
  as select from zbs_dmo_discount
  association [0..1] to ZBS_I_DmoPartner  as _Partner  on $projection.PartnerNumber = _Partner.PartnerNumber
  association [0..1] to ZBS_I_DmoMaterial as _Material on $projection.MaterialNumber = _Material.MaterialNumber
{
  key partner  as PartnerNumber,
  key material as MaterialNumber,
      discount as DiscountValue,
      _Partner,
      _Material
}

 

Data-Preview

The associations help to navigate through the data and provide additional information. We can show you that quite well with the Eclipse data preview. To do this, we call up the data preview from the CDS view "ZBS_I_DmoPosition" and call up the context menu for a data record.

 

Via "Follow Association" we get a suggestion for all defined associations for further navigation:

 

So we can navigate from the items, through the header data and the assigned partner to the currency and get the corresponding data.

 

Complete document

In the last article we defined the CDS view "ZBS_I_DmoCompleteDocument" and combined the data we needed with joins. With the associations we now have the possibility to do the same without an additional object or join. To do this, we can navigate via the field list of the SELECT and the individual annotations. The select could now look like this:

SELECT FROM ZBS_I_DmoPosition
  FIELDS  DocumentNumber, 
          PositionNumber, 
          \_Invoice-PartnerNumber, 
          \_Invoice\_Partner-PartnerName,
          \_Invoice\_Partner-City, 
          \_Invoice\_Partner-Country,
          MaterialNumber,
          PositionQuantity,
          PositionPrice,
          PositionCurrency,
          \_Invoice-DocumentDate
  INTO TABLE @DATA(lt_full_with_association)
  UP TO 20 ROWS.

 

Conclusion

The association adds relationships and context information to the entire data model without impairing performance. You should use this to supply all your interface views with the appropriate data so that you can use them later for access without having to expand the data model.


Included topics:
CDSCore Data ServiceAssociation
Comments (0)



And further ...

Are you satisfied with the content of the article? We post new content in the ABAP area every Friday and irregularly in all other areas. Take a look at our tools and apps, we provide them free of charge.


ABAP Tools - Work with Eclipse (CDS Analysis)

Category - ABAP

In the complex world of CDS views, it is important to have the right tool at hand to ensure an analysis of the structures and data sources.

08/25/2023

CDS - View Entity

Category - ABAP

In this article you will learn more about the new Core Data Service entities, what they bring and what makes them different from the old views.

07/29/2022

CDS - Learnings

Category - ABAP

In this article we summarize the learned content and show you the way for what you will need the CDS Views in the future.

06/10/2022

CDS - Virtual fields

Category - ABAP

This article is about virtual fields in Core Data Services and how you can subsequently deliver such complex data.

06/03/2022

CDS - Authority check

Category - ABAP

How and where are the permissions for a Core Data Service delimited? Check out this article for the details.

05/20/2022