How to Join Multiple Tables in a CDS View and Select Specific Fields?

When working in SAP, there are many instances where data from multiple tables is required to create a complete and meaningful dataset for analysis or reporting. Core Data Services (CDS) views make this easier by allowing developers to join tables and select specific fields, reducing the need for custom ABAP code. In this guide, we’ll walk through a business scenario where joining multiple tables in a CDS view is essential, and then provide step-by-step instructions to create a CDS view that performs these joins and selects specific fields.

Business Scenario

Imagine you are working as an SAP consultant for a retail company that needs a comprehensive view of its vendor and purchasing data. The procurement team has requested a report showing vendor details (such as name and city) alongside purchasing data like purchase organization and payment terms. This data exists in different SAP tables: LFA1 (Vendor Master), LFB1 (Company Code Data for Vendor), and EKPO (Purchasing Document Item). The challenge is to bring together relevant data from these tables in a single view for easier reporting and analysis.

Using a CDS view with table joins, we can efficiently retrieve the desired data without custom ABAP code. Let’s dive into how to do this.

Step 1: Understand the Table Relationships

Before we create the CDS view, it’s essential to understand the relationships between the tables:

  1. LFA1 (Vendor Master) holds general information about vendors.
  2. LFB1 (Company Code Data for Vendor) has vendor financial information and is connected to LFA1 through the LIFNR (Vendor Number) field.
  3. EKPO (Purchasing Document Item) contains item-level purchasing data and links to LFA1 through LIFNR.

These relationships will guide how we structure our joins in the CDS view.

Step 2: Creating the CDS View with Multiple Joins

To create a CDS view that joins LFA1, LFB1, and EKPO and selects specific fields, we’ll use the ABAP Development Tools (ADT) in Eclipse. Follow these steps:

2.1 Define the CDS View

Open the ABAP Development Tools (ADT) in Eclipse and create a new CDS view.

  1. Right-click on the package where you want to create the CDS view.
  2. Select New > Other ABAP Repository Object > Data Definition.
  3. Give it a name, such as ZVENDOR_PURCHASE_VIEW, and provide a meaningful description.

2.2 Write the CDS Code

Use the following code template to define the joins and select fields. In this example, we’ll select specific fields from each table:

ABAPCopy code@AbapCatalog.sqlViewName: 'ZVENDPURCHVW'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS View for Vendor and Purchase Data'

define view ZVENDOR_PURCHASE_VIEW as select from lfa1
    inner join lfb1 on lfa1.lifnr = lfb1.lifnr
    inner join ekpo on lfa1.lifnr = ekpo.lifnr
{
    key lfa1.lifnr        as Vendor,          // Vendor Number
    lfa1.name1            as VendorName,      // Vendor Name
    lfa1.ort01            as City,            // City
    lfb1.bukrs            as CompanyCode,     // Company Code
    lfb1.zterm            as PaymentTerms,    // Payment Terms
    ekpo.ebeln            as PurchaseOrder,   // Purchase Order Number
    ekpo.ebelp            as ItemNumber       // Item Number in Purchase Order
}

This code does the following:

  • Joins LFA1 to LFB1 on LIFNR.
  • Joins LFA1 to EKPO on LIFNR.
  • Selects key fields like vendor number, vendor name, city, company code, payment terms, purchase order number, and item number.

Explanation of Annotations

  • @AbapCatalog.sqlViewName: Specifies the SQL view name in the database.
  • @AccessControl.authorizationCheck: Controls authorization. Here, we disable it with #NOT_REQUIRED for simplicity.
  • @EndUserText.label: Provides a label for the CDS view.

Step 3: Activate and Test the CDS View

After defining the CDS view, activate it. Activation creates an SQL view with the name ZVENDPURCHVW in the database.

To test the view:

  1. Open ABAP Development Tools and run a data preview by right-clicking the CDS view and selecting Open with > Data Preview.
  2. Verify that the output includes the selected fields from all three tables, with data accurately joined.

Step 4: Use the CDS View in an OData Service (Optional)

If the procurement team wants to access this data through an SAP Fiori application, you can expose this CDS view as an OData service. Here’s how:

Add @OData.publish: true annotation to your CDS view:

@OData.publish: true

Register the OData service in SAP Gateway using transaction /IWFND/MAINT_SERVICE.

Access the OData service and bind it to an SAP Fiori application or use it directly in analytical tools.

Interview Tips: CDS Views with Multiple Joins

  1. Explain CDS vs. Traditional ABAP: CDS views simplify complex data retrieval processes that would require more custom ABAP code, making them ideal for analytical applications.
  2. Highlight Performance Optimization: Emphasize the performance benefits of using CDS views, especially when they leverage HANA’s in-memory processing for large datasets.
  3. Security Features: Mention the @AccessControl.authorizationCheck annotation as a way to manage access control at the CDS view level.
  4. Code Reusability: CDS views are reusable, meaning that once created, they can be used across various applications, reducing redundancy.
  5. Business Scenario: During interviews, try to relate your experience with CDS views to real-world business needs, such as reporting or data extraction.

Key Takeaways

By following this guide, you now have a CDS view that joins data from multiple tables to produce a comprehensive report, meeting your business requirements with minimal ABAP coding. This method reduces redundancies, provides flexibility, and is compatible with SAP’s modern data access paradigms. Additionally, understanding how to join tables in CDS views and select specific fields is a skill that is often required and valued in SAP development roles, especially for building data-intensive applications and reports.

Leave a Comment