Architect / Cross-trackintermediate

CDS Views Part 6: Associations and Cardinality

Key takeaway

Model relations between CDS views with associations instead of hard joins: syntax, ON conditions with $projection, cardinality [0.. 1], [1.. 1], [0.. ], path expressions, exposing associations to consumers, and the row-multiplication trap.

Explanation

📌 KEY POINTS
✅ An association is a declared relation ("join on demand"): the join is only executed when a consumer actually uses a field of the associated view.
✅ Cardinality [0..1], [1..1], [0..*], [1..*] describes how many target rows exist per source row — it documents intent and helps the optimizer.
✅ Exposing the association (listing _Customer in the field list) lets consumers, OData and Fiori navigate to it.
⚠️ Using a path expression to a to-many association ([0..*]) in the field list multiplies rows — the classic cause of doubled totals.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 5 already used an association between composite and basic views. Here is how associations really work.

━━━━━━━━━━━━━━━━━━━━
▶ 1. SYNTAX

association [cardinality] to <target> as _Alias on <condition>
The alias starts with underscore by convention (_Customer, _Item).
$projection.<field> refers to a field of the current view by its alias name.

━━━━━━━━━━━━━━━━━━━━
▶ 2. JOIN ON DEMAND

Declaring an association costs nothing.
Using _Customer.CustomerName in the field list creates a left outer join in the generated SQL.
Consumers selecting only header fields never trigger the join.

━━━━━━━━━━━━━━━━━━━━
▶ 3. CARDINALITY

[0..1] or [1]: at most one target (order → customer).
[1..1]: exactly one target.
[0..*] or [*]: many targets (order → items).
Wrong cardinality does not cause an error but can mislead the optimizer and consumers.
Newer releases offer an explicit syntax (e.g. "of exact one to many"); the bracket form remains valid.

━━━━━━━━━━━━━━━━━━━━
▶ 4. PATH EXPRESSIONS

_Customer.CustomerName: pulls one field from the target.
Filtered path: _Text[1: Language = $session.system_language].Description — reduces to-many to to-one.
To-many paths in the field list add one row per target row.

━━━━━━━━━━━━━━━━━━━━
▶ 5. EXPOSED ASSOCIATIONS

Listing _Customer in the field list publishes the association.
Consumer views can follow it further; OData generates navigation properties.
Fields used in the ON condition must be part of the field list.

━━━━━━━━━━━━━━━━━━━━
▶ 6. ASSOCIATION VS JOIN

Join: always executed, fixed.
Association: executed only if used, reusable, navigable, self-documenting.
Use joins for mandatory relations needed by every consumer; associations for optional and reusable relations.

━━━━━━━━━━━━━━━━━━━━
▶ 7. CLASSIC ABAP CONSUMERS

Classic Open SQL reads flattened fields (CustomerName) that the view has already resolved.
ABAP SQL path expressions (\_Customer-CustomerName) need strict modern syntax — use them only where modern syntax is allowed.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP

Build ZI_SalesOrderAssoc with _Customer [1..1] and _Item [0..*].
Add CustomerName via path expression — row count stays the same.
Add _Item.Material via path — observe duplicated header rows in Data Preview; remove it again.
Expose _Customer and _Item; follow them in ADT Data Preview (right-click → Follow Association).
Read flattened header fields from a classic report.

✅ CHECKPOINT: you can declare associations with correct cardinality and avoid row multiplication.

Code example

ABAP Code

// ===== STEP 1: associations with cardinality =====@AccessControl.authorizationCheck: #NOT_REQUIRED@EndUserText.label: 'Sales order with associations'define view entity ZI_SalesOrderAssoc  as select from vbak  association [1..1] to ZI_Customer          as _Customer    on $projection.SoldToParty = _Customer.Customer  association [0..*] to ZI_SalesOrderItemEnh as _Item    on $projection.SalesOrder = _Item.SalesOrder{  key vbeln as SalesOrder,      erdat as CreationDate,      kunnr as SoldToParty, // ===== STEP 2: to-one path - safe, row count unchanged =====      _Customer.CustomerName as CustomerName, // ===== STEP 3: to-many path - AVOID here, duplicates headers =====//    _Item.Material as Material,       @Semantics.amount.currencyCode: 'TransactionCurrency'      netwr as NetAmount,      waerk as TransactionCurrency, // ===== STEP 4: expose associations for consumers / OData =====      _Customer,      _Item} * ===== STEP 5: classic report reads flattened fields =====REPORT zcds_so_with_customer. TYPES: BEGIN OF ty_so,         salesorder   TYPE vbeln_va,         customername TYPE name1_gp,         netamount    TYPE netwr_ak,         transactioncurrency TYPE waerk,       END OF ty_so. DATA: lt_so    TYPE STANDARD TABLE OF ty_so,      ls_so    TYPE ty_so,      lv_lines TYPE i. START-OF-SELECTION.  SELECT salesorder customername netamount transactioncurrency    FROM zi_salesorderassoc    INTO TABLE lt_so    WHERE creationdate >= '20260101'.   DESCRIBE TABLE lt_so LINES lv_lines.  WRITE: / 'Orders:', lv_lines.   "one row per order - no duplicates   LOOP AT lt_so INTO ls_so.    WRITE: / ls_so-salesorder, ls_so-customername,             ls_so-netamount CURRENCY ls_so-transactioncurrency.  ENDLOOP. * 💡 Modern syntax (strict ABAP SQL, 7.50+):*   SELECT salesorder, \_customer-customername AS name*     FROM zi_salesorderassoc INTO TABLE @DATA(lt_modern).

Real project scenario

A finance user reported that the order value dashboard showed exactly three times the real value for some customers. The CDS view had a path expression _Item.Material in its field list to show "first material", which created one header row per item; SUM(NetAmount) then counted each header several times.
The fix removed the to-many path from the header view, exposed _Item as an association instead, and built a separate item-level view for material analysis. Totals matched SD reports again.

Common mistakes

To-many path expressions in header-level views, multiplying rows and totals.
Wrong cardinality ([1..1] declared for a relation that can have zero or many rows).
Forgetting to include ON-condition fields in the field list.
Replacing every association with joins, losing join-on-demand and navigation.
Using the association alias without underscore, confusing readers.

Best practices

Declare cardinality honestly; test with real data.
Only use to-one paths (or filtered paths reducing to one) in field lists.
Expose associations so consumers and OData can navigate.
Prefix association names with underscore.
Check row counts before and after adding a path expression.

Interview angle

Questions: "Difference between association and join in CDS?", "What does cardinality do?", "Why might an association double my totals?"
Strong answer: associations are joins on demand — executed only when a consumer uses a target field — reusable and navigable (OData navigation properties); joins are always executed. Cardinality documents to-one/to-many and helps the optimizer. A path to a to-many association in the field list returns one row per target, multiplying header rows and aggregated values; use filtered paths or separate item views.

Practice this lesson

Quick check

1. A header view lists _Item.Material, where _Item is a [0..*] association to items. What happens?

2. What does the cardinality [0..*] on a CDS association express?

3. Associations in CDS are:

Interview questions

1. A CDS-based Fiori app shows duplicate rows. What would you check?

Answer it in your own words first, then compare.

2. What is an association in CDS?

Answer it in your own words first, then compare.

Practice more with Interview AI →

Reference-verified: the steps are checked against official SAP documentation and tutorials, and object and field names in the sample code are checked for consistency. The code has not been compiled or run on an SAP system by ERPClimb. Syntax marked release-dependent varies by SAP release, so check it in your own system.