RAP / CDS / ODataintermediate

CDS Associations Part 2: Path Expressions and How Associations Become Joins

Key takeaway

See exactly how associations turn into SQL: path expressions in views, in ABAP SQL and in OData, the default LEFT OUTER JOIN, filtered paths, and join-type attributes.

Explanation

📌 KEY POINTS
✅ A path expression (_Customer.CustomerName) is what turns an association into a join.
✅ The default join is LEFT OUTER — rows without a target are kept.
✅ Filters in square brackets restrict the target: _Item[ Material = 'X' ].
⚠️ Each path is joined once per distinct path — but to-many paths still multiply rows.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 1 declared associations. Now you use them. Every use of an association is a path expression, and every path expression becomes a join in the generated SQL.

━━━━━━━━━━━━━━━━━━━━
▶ 1. PATH EXPRESSIONS IN A CDS VIEW
In the element list of another view:
_Customer.CustomerName as CustomerName
The database now joins ZI_SalesOrder with ZI_Customer. Several fields over the same path (_Customer.CustomerName, _Customer.Country) produce only one join.
Paths can be chained: _Customer._Country.CountryName — one join per step.

━━━━━━━━━━━━━━━━━━━━
▶ 2. PATH EXPRESSIONS IN ABAP SQL
In ABAP SQL the syntax uses a backslash and a dash:
SELECT SalesOrder, \_Customer-CustomerName AS CustomerName FROM zi_salesorder …
Chained: \_Customer\_Country-CountryName.
💡 TIP: this lets ABAP code reuse relationships from CDS instead of re-coding joins.

━━━━━━━━━━━━━━━━━━━━
▶ 3. IN ODATA
Consumers follow associations with $expand (e.g. ?$expand=_Item) or navigation URLs. The join happens only for requests that ask for it. This is why RAP projection views expose associations.

━━━━━━━━━━━━━━━━━━━━
▶ 4. THE GENERATED JOIN TYPE
By default a path becomes a LEFT OUTER JOIN: orders without a customer still appear, with empty customer fields. This is usually what you want.
If you need an inner join semantics for a path, specify it as an attribute: _Customer[inner].CustomerName.
⚠️ WATCH OUT: inner joins through paths silently remove rows. Use them only when missing targets really mean "exclude the row".

━━━━━━━━━━━━━━━━━━━━
▶ 5. FILTERED PATHS
A filter in square brackets restricts the target rows:

_Item[ ItemCategory = 'TAN' ].Material
_Text[ Language = 'E' ].Description

If the filter guarantees at most one row, add the cardinality: _Item[ 1: SalesOrderItem = '000010' ].Material. This declares the path to-one and prevents row multiplication.

━━━━━━━━━━━━━━━━━━━━
▶ 6. WHERE PATHS APPEAR MATTERS

In the element list — adds columns (and possibly rows, if to-many).
In WHERE — filters on the target; with to-many targets this can return a source row several times.
In GROUP BY / aggregation — aggregate over the target rows.

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

Build ZC_SalesOrderList (code example) with two to-one paths and one filtered path.
In ADT, open the view and use "Show SQL CREATE Statement" to see the generated joins.
Add _Item.Material without a filter and compare the row count in Data Preview — you will see the multiplication (Part 3 explains the fix).

✅ CHECKPOINT: you can predict the SQL behind any path expression.

Code example

ABAP Code

// ===== Consumption view using path expressions =====@AccessControl.authorizationCheck: #CHECK@EndUserText.label: 'Sales Order List'define view entity ZC_SalesOrderList  as select from ZI_SalesOrder{  key SalesOrder,      OrderDate,       // to-one paths: one LEFT OUTER JOIN to ZI_Customer, reused for both fields      _Customer.CustomerName          as CustomerName,      _Customer.Country               as CustomerCountry,       // chained path: customer -> country text (language condition inside the association)      _Customer._CountryText.CountryName as CustomerCountryName,       // filtered path, declared to-one with "1:" -> no row multiplication      _Item[ 1: SalesOrderItem = '000010' ].Material as FirstItemMaterial,       // text via to-one association with language condition (Part 1)      _StatusText.StatusText          as StatusText,       _Item                            // still exposed for $expand} // ===== The same idea in ABAP SQL =====" SELECT SalesOrder,"        \_Customer-CustomerName      AS CustomerName,"        \_Customer\_CountryText-CountryName AS Country   " chained"   FROM zi_salesorder"   WHERE \_Customer-Country = 'DE'                      " filter on the target"   INTO TABLE @DATA(german_orders). // ===== Inner join semantics only when intended =====//   _Customer[inner].CustomerName   -> orders WITHOUT customer are removed! // ===== WRONG (multiplies rows): to-many path without filter =====//   _Item.Material as Material      -> one row per item

Real project scenario

A report on German customers' orders used "WHERE \_Customer-Country = 'DE'" in ABAP SQL and worked well. A developer copied the pattern for "orders containing material X" as "WHERE \_Item-Material = 'X'". Orders with that material in three items appeared three times, and a subsequent sum of order values was inflated.
The team explained the difference: the customer path is to-one, the item path is to-many. The fix used a separate query on items to get distinct order numbers (or an aggregated view per order), and the sum was calculated on the de-duplicated set.

Common mistakes

Assuming a path does not cost a join.
Using to-many paths in the element list or WHERE without a filter.
Forcing [inner] paths and silently losing rows.
Filtering a path without adding "1:" when the filter really returns one row.
Re-coding joins in ABAP instead of using \_association paths.
Not checking the generated SQL when results look strange.

Best practices

Use to-one paths freely; treat to-many paths as a deliberate decision.
Use filtered paths with cardinality to get exactly one target row.
Keep LEFT OUTER semantics unless excluding rows is the requirement.
Inspect "Show SQL CREATE Statement" for complex views.
Let OData consumers use $expand instead of flattening to-many data into lists.

Interview angle

Questions: "What is a path expression?", "Which join does it generate?", "What is a filtered path?", "How do you use associations in ABAP SQL?"
Strong answer: a path expression follows an association and generates a join — by default LEFT OUTER — once per distinct path; filters in brackets restrict the target and "1:" declares a to-one result; [inner] changes the join type. In ABAP SQL the syntax is \_Assoc-Field. Explain that to-many paths multiply rows, with a concrete example.

Practice this lesson

Quick check

1. Which ABAP SQL statement reads the customer name through the association _Customer of ZI_SalesOrder?

2. What best describes a 'filtered association' in ABAP CDS?

3. A CDS view uses a path expression such as '_Supplier.Country = \'DE\'' inside its WHERE clause. What does this pattern accomplish?

Interview questions

1. Explain path expressions: what SQL do they generate, how do filtered paths work, and how are they written in ABAP SQL?

Answer it in your own words first, then compare.

2. A universal sales order view uses inner joins to customer, material and plant, and some orders disappear. How would you redesign it?