CDS Views Part 2: Joins, Expressions, CASE, CAST and Functions

📌 KEY POINTS
✅ CDS supports inner join, left outer join, right outer join, cross join and union — far beyond SE11 views.
✅ Calculated fields (CASE, CAST, arithmetic, string and date functions) run in the database, so ABAP receives finished values.
✅ Session variables ($session.system_date, $session.user, $session.system_language, $session.client) give runtime context without parameters.
⚠️ Every calculation needs a stable result type — use CAST to fix length and type, especially in CASE branches that return literals of different lengths.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 1 created a single-table view. Now we combine tables and calculate values in the database.

━━━━━━━━━━━━━━━━━━━━
▶ 1. JOINS

• inner join: only rows with a match on both sides (order item + its header).
• left outer join: all rows from the left side, NULL/initial if no match (items with optional text).
• Give each data source an alias (as item, as head) and prefix fields with it.
• Join conditions use = on key fields; avoid functions in ON conditions (performance, Part 10).
• For reusable relations prefer associations (Part 6) over hard joins.

━━━━━━━━━━━━━━━━━━━━
▶ 2. WHERE IN THE VIEW

• A WHERE clause in the view is a fixed filter for every consumer (e.g. only order type TA).
• Put only rules that are always true into the view; consumer-specific filters belong in the consumer's SELECT.

━━━━━━━━━━━━━━━━━━━━
▶ 3. CASE

• Simple CASE: case field when 'A' then … else … end.
• Searched CASE: case when cond then … end — for ranges and multiple fields.
• Cast literals to the same type so all branches return one type.

━━━━━━━━━━━━━━━━━━━━
▶ 4. CAST AND ARITHMETIC

• cast( field as abap.char(10) ), abap.int4, abap.dec(15,2), abap.fltp, or a data element (cast( x as vbeln_va )).
• Division of integers returns an integer — cast to decimal first, or use division( a, b, dec ).

━━━━━━━━━━━━━━━━━━━━
▶ 5. BUILT-IN FUNCTIONS

• Strings: concat, concat_with_space, substring, left, right, lpad, replace, upper, lower.
• Dates: dats_days_between, dats_add_days, dats_add_months, dats_is_valid.
• Numbers: abs, ceil, floor, round, division, mod.
• Currency and unit: currency_conversion, unit_conversion (use carefully, Part 10).

━━━━━━━━━━━━━━━━━━━━
▶ 6. SESSION VARIABLES

• $session.system_date: today's date on the application server.
• $session.user, $session.system_language, $session.client.
• Useful for "age of order in days" or language-dependent texts without adding parameters.

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

1. Create ZI_SalesOrderItemEnh: VBAP inner join VBAK.
2. Add RejectionStatus with a searched CASE on ABGRU and CAST to abap.char(10).
3. Add SalesArea with concat and AgeInDays with dats_days_between and $session.system_date.
4. Preview with F8, then read the view from a classic report sorted by AgeInDays.

✅ CHECKPOINT: you can build a joined view with calculated fields that returns finished data to ABAP.

A backlog report for a pharma client read VBAP and VBAK in nested SELECTs, then calculated order age and status in ABAP loops. For 2 million items it ran 25 minutes.
The team moved the join, the rejection status CASE and the age calculation into one CDS view entity. The report became a single SELECT with a WHERE on RejectionStatus; runtime dropped to under a minute, and the same view fed a Fiori backlog tile.

• CASE branches returning literals of different lengths without CAST (type conflict or truncated values).
• Putting consumer-specific filters (a single sales org) into the view WHERE, making it unusable for others.
• Using left outer join where inner join is meant, then being surprised by empty fields.
• Integer division producing 0 instead of a decimal ratio.
• Using functions on columns in join conditions, blocking index use.

• Alias every data source and prefix every field.
• CAST calculated fields to explicit types or data elements.
• Keep fixed business rules in the view, variable filters in the consumer.
• Use $session variables instead of parameters for today's date or logon language.
• Prefer associations for optional, reusable relations (Part 6).

Questions: "Which joins does CDS support?", "How do you calculate a field in CDS?", "What are session variables?"
Strong answer: inner, left/right outer, cross join and union; calculated fields with CASE, CAST, arithmetic and built-in string/date/number functions executed in the database; CAST to fix result types; $session.system_date, user, system_language and client provide runtime context. Mention keeping fixed rules in the view and variable filters in the consumer.