Lesson 4 Practice — Booking Details with CDS Joins

The guided lesson combined:

Connection
Carrier
Origin Airport
Destination Airport

into one route model.

In this practice, solve a different relational problem:

A booking stores IDs for a customer and carrier. Build a CDS model that exposes useful booking details together with the related customer and carrier information.

Your architecture will be:

/DMO/CUSTOMER ──┐
                 │
/DMO/BOOKING ────┼──→ ZI_BOOKING_DETAIL_###
                 │
/DMO/CARRIER ────┘
                         ↓
                  Booking Reader
                         ↓
                       Runner

The complete solution is intentionally not included here.

---

Objects to create

ZI_BOOKING_DETAIL_###
ZCL_BOOKING_READER_###
ZCL_CDS_L04_PRACTICE_###

Replace ### with your own student/group suffix.

---

Part 1 — Inspect /DMO/BOOKING

Open:

/DMO/BOOKING

with:

Ctrl + Shift + A

Identify:

travel_id
booking_id
booking_date
customer_id
carrier_id
connection_id
flight_date
flight_price
currency_code

Confirm that the booking key is:

travel_id + booking_id

Open Data Preview.

Record:

one Customer ID:
one Carrier ID:

from a booking row.

---

Part 2 — Inspect /DMO/CUSTOMER

Open:

/DMO/CUSTOMER

Find:

customer_id
first_name
last_name

Find the customer ID you recorded.

Record:

First name:
Last name:

---

Part 3 — Inspect /DMO/CARRIER

Open:

/DMO/CARRIER

Find:

carrier_id
name
currency_code

Find the carrier ID you recorded.

Record:

Carrier name:

---

Part 4 — Design the result model

Your CDS entity must expose:

TravelId
BookingId
BookingDate

CustomerId
CustomerFirstName
CustomerLastName
CustomerName

CarrierId
CarrierName

ConnectionId
FlightDate
FlightPrice
CurrencyCode

CustomerName should be calculated from:

first name + one space + last name

using the CDS expression skills from Lesson 3.

---

Part 5 — Draw the relationships

Complete:

Booking.customer_id
        ↓
____________________.____________________

Booking.carrier_id
        ↓
____________________.____________________

Then answer:

Do the target fields identify the corresponding target records?

---

Part 6 — Create the Data Definition

Create:

ZI_BOOKING_DETAIL_###

Description:

Lesson 4 - Booking Detail

Choose a CDS View Entity template.

Use:

/DMO/BOOKING

as the primary source with alias:

Booking

---

Part 7 — Join Customer

Join:

/DMO/CUSTOMER

with alias:

Customer

Use:

INNER JOIN

The ON condition must match the booking's customer ID to the customer record.

Hint

The shape is:

inner join /dmo/customer as Customer
  on Booking.__________ = Customer.__________

---

Part 8 — Join Carrier

Join:

/DMO/CARRIER

with alias:

Carrier

Use:

INNER JOIN

The ON condition must connect the booking's carrier ID with the carrier key.

---

Part 9 — Define the CDS keys

The CDS entity represents:

a booking

Mark as keys only the elements needed to identify a booking.

Do not mark these as keys:

CustomerName
CarrierName
FlightPrice

---

Part 10 — Expose customer information

Expose:

CustomerId
CustomerFirstName
CustomerLastName

Use qualified source references:

Booking.<field>
Customer.<field>

Make the data source of each element obvious.

---

Part 11 — Calculate CustomerName

Use:

CONCAT_WITH_SPACE

The result should resemble:

Jane Smith

rather than:

JaneSmith

Hint

The shape is:

concat_with_space(
  Customer.__________,
  Customer.__________,
  1
) as CustomerName

---

Part 12 — Expose carrier and booking details

Expose:

CarrierId
CarrierName
ConnectionId
FlightDate
FlightPrice
CurrencyCode

CarrierName must come from:

Carrier

---

Part 13 — Syntax check and activate

Run:

Ctrl + F2

Fix all errors.

Activate:

Ctrl + F3

Preview:

F8

---

Part 14 — Verify one joined row

Choose one booking result.

Compare:

CustomerId
CustomerName
CarrierId
CarrierName

with the independent Data Preview results of:

/DMO/CUSTOMER
/DMO/CARRIER

Record:

Travel ID:
Booking ID:
Customer match verified: Yes / No
Carrier match verified:  Yes / No

---

Part 15 — Check for row multiplication

Inspect whether the same:

TravelId + BookingId

appears unexpectedly more than once.

If it does:

1. inspect the target keys;
2. inspect the ON conditions;
3. determine whether one booking is matching several target rows.

Do not immediately add:

DISTINCT

Answer:

Why can an incomplete join condition produce several rows for one source record?

---

Part 16 — SQL Console

Query:

ZI_BOOKING_DETAIL_###

Return at least:

TravelId
BookingId
CustomerName
CarrierId
CarrierName
ConnectionId
FlightDate
FlightPrice
CurrencyCode

Order by:

TravelId
BookingId

---

Part 17 — Filter by carrier

Use the carrier ID recorded in Part 1.

Add a SQL Console filter for:

CarrierId

Confirm that every result row belongs to that carrier.

---

Part 18 — Create the Booking Reader

Create:

ZCL_BOOKING_READER_###

The Reader should not implement:

IF_OO_ADT_CLASSRUN

Define:

TT_BOOKINGS

as a standard table based directly on:

ZI_BOOKING_DETAIL_###

with:

WITH EMPTY KEY

---

Part 19 — Add GET_ALL

Create:

GET_ALL

Requirements:

• return TT_BOOKINGS;
• select from ZI_BOOKING_DETAIL_###;
• use FIELDS *;
• order by TravelId, BookingId;
• contain no direct Customer or Carrier join syntax.

---

Part 20 — Add GET_BY_CARRIER

Create:

GET_BY_CARRIER

Import:

carrier_id

Return:

TT_BOOKINGS

Requirements:

• select from ZI_BOOKING_DETAIL_###;
• filter on CDS element CarrierId;
• use an ABAP SQL host variable;
• order by TravelId, BookingId.

---

Part 21 — Create the Runner

Create:

ZCL_CDS_L04_PRACTICE_###

Implement:

IF_OO_ADT_CLASSRUN

In main:

1. instantiate the Booking Reader;
2. call GET_ALL;
3. loop over the result;
4. display a readable line containing:

TravelId/BookingId
CustomerName
CarrierName

Design the exact output yourself.

---

Part 22 — Test GET_BY_CARRIER

Use a carrier ID present in the current tenant.

Call:

GET_BY_CARRIER

Display the returned bookings.

Confirm:

Reader
  ↓
ZI_BOOKING_DETAIL_###

and not:

Reader
  ↓
direct Customer/Carrier joins

---

Part 23 — Explain INNER JOIN

In your own words:

What happens to a booking if its customer_id has no matching /DMO/CUSTOMER row?

What happens to a booking if its carrier_id has no matching /DMO/CARRIER row?

---

Part 24 — Think about LEFT OUTER JOIN

Without changing your final solution yet, answer:

If the Customer join changed to LEFT OUTER JOIN, what would happen to a booking with no matching Customer row?

What would happen to the Customer-side values at SQL level?

Would changing only the Customer join change the Carrier join's behavior?

---

Part 25 — Join-type experiment

Make a temporary copy or temporary edit.

Change:

Customer INNER JOIN

to:

Customer LEFT OUTER JOIN

Activate and preview.

Then restore your intended final version.

If the visible result does not change, explain why complete demo reference data can make the two previews look the same even though the join semantics differ.

---

Part 26 — Reasoning questions

Answer in your own words.

1. What problem does a join solve?

2. What is the purpose of an ON condition?

3. Why should target keys be inspected before writing a join?

4. Why are TravelId and BookingId still the keys of the joined model?

5. Why is CustomerName not a key?

6. Why does CarrierName come from /DMO/CARRIER?

7. What is the difference between INNER JOIN and LEFT OUTER JOIN?

8. How can one source row become several result rows?

9. Why should unexpected duplicates be investigated before using DISTINCT?

10. Why is the Reader simpler after the join is modeled in CDS?

11. Why are meaningful aliases useful?

12. Why will associations be useful after learning joins?

---

Optional challenge 1 — Customer location

Expose:

CustomerCity
CustomerCountry

from /DMO/CUSTOMER.

Filter the joined CDS entity in SQL Console by a country present in your data.

---

Optional challenge 2 — Add Connection

Join:

/DMO/CONNECTION

to the booking model.

Before coding, inspect its key:

carrier_id
connection_id

Your ON condition must represent the complete relationship.

Expose:

DepartureTime
ArrivalTime

Then answer:

What could happen if you joined only on connection_id and ignored carrier_id?

Hint

A complete relationship needs both booking fields that identify the connection.

---

Submission checklist

• ZI_BOOKING_DETAIL_### exists.
• It uses define view entity.
• /DMO/BOOKING is the primary source.
• /DMO/CUSTOMER is joined with alias Customer.
• /DMO/CARRIER is joined with alias Carrier.
• Both joins use correct ON conditions.
• TravelId and BookingId are CDS keys.
• Customer fields come from the correct source.
• Carrier name comes from the correct source.
• CustomerName uses CONCAT_WITH_SPACE.
• The CDS entity activates.
• Data Preview has been verified against source data.
• Unexpected duplicate booking keys have been investigated.
• SQL Console can query the joined model.
• SQL Console carrier filtering works.
• ZCL_BOOKING_READER_### uses the CDS entity as its table row type.
• GET_ALL selects from the CDS entity.
• GET_BY_CARRIER uses a host-variable filter.
• The Reader contains no Customer/Carrier join syntax.
• The Runner contains no direct database joins.
• INNER JOIN vs LEFT OUTER JOIN is explained.
• Reasoning questions are answered.

---

Useful ADT shortcuts

Action	Shortcut
Open ABAP Development Object	Ctrl + Shift + A
Code completion	Ctrl + Space
Syntax check	Ctrl + F2
Activate current object	Ctrl + F3
Activate inactive objects	Ctrl + Shift + F3
CDS Data Preview	F8
Run console class	F9
Format source	Shift + F1