CDS - Association
This article is about associations, which provide functionality similar to joins. But we will show you what the differences are.
Table of contents
Article update: Since release 7.57 (S/4 HANA 2022), DEFINE VIEW is marked as obsolete, you should use DEFINE VIEW ENTITY instead. These may differ from the examples in some places. You can find more information about the new views in this article.
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.
