How to use JOINS in ABAP CDS Views on HANA

3
8679

Hello everyone, in this ABAP on HANA/ABAP in Eclipse tutorial, we will learn how to use JOINS in ABAP CDS Views on HANA. The abbreviation for CDS is Core Data Services.To know more about ABAP CDS Views click here.

As an ABAP developer, we all know about JOINS and how to use them in our ABAP program which are running on traditional databases.But our hands are tied up and we are refrained to use these JOINS due to performance issues in traditional databases. As an when SAP introduced HANA database no more restrictions in using them and we are free to use as many as JOINS on different tables. Thanks to SAP HANA.

In this tutorials we will see different types of JOINS available and we will learn how to use JOINS in ABAP CDS Views on HANA.

So lets have a look on how to use JOINS in ABAP CDS Views on HANA. In this demo example we are using EPM tables SNWD_SO(EPM: Sales Order Header Table), SNWD_SO_I(EPM: Sales Order Item Table) and SNWD_PD(EPM: Product Data Table).

Step-by-Step Procedure

1. Choose the package in which you want to create CDS Views.Right click on the package → New → Other ABAP Repository Object.

New ABAP Repository Object

2. In the New ABAP Repository Object window, search for DDL source object by typing in search field.Select the DDL Source and hit Next.

Search for DDL Source

3. In the New DDL Source window, enter Name and Description of the CDS View and hit Finish.

Creat a DDL Source

4. A new CDS view editor opens up like below.

ABAP CDS View-0

Different Types of JOINS

1INNER JOIN or just JOIN

Selects all entries which exists in both tables which meet ON condition. Below is the sample ABAP CDS views in which INNER JOIN is used among 3 tables to get the so_id, so_item_pos, product_id, amount and  currency_code. Copy and paste the below code into DDL source

@AbapCatalog.sqlViewName: 'ZV_DEMO_01'
@ClientDependent: true
@AbapCatalog.compiler.CompareFilter: true
@EndUserText.label: 'Demo'
define view Zcds_Demo_Example1 as 
select from snwd_so
       join snwd_so_i on snwd_so.node_key = snwd_so_i.parent_key
       inner join snwd_pd on snwd_so_i.product_guid = snwd_pd.node_key
{
    snwd_so.so_id,    
    snwd_so_i.so_item_pos,
    snwd_pd.product_id,
    snwd_so_i.net_amount,
    snwd_so_i.currency_code
}

2LEFT OUTER JOIN

Selects all records from the left table matching with the right table which meet ON condition. If no record found in second table those record fields are null.

@AbapCatalog.sqlViewName: 'Z_CDS_OUTJOIN'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Joins in ABAP CDS Views'
define view Z_Cds_Joins as select from snwd_so
       left outer join snwd_so_i on snwd_so.node_key = snwd_so_i.parent_key
{
    snwd_so.so_id, 
    snwd_so.billing_status,
    snwd_so.delivery_status,
    snwd_so.op_id,
    snwd_so_i.so_item_pos,    
    snwd_so_i.net_amount,
    snwd_so_i.currency_code
}

 

3RIGHT OUTER JOIN

Selects all records from the right table matching with the left table which meet ON condition. If no record found in left table those record fields are null.

@AbapCatalog.sqlViewName: 'Z_CDS_OUTJOIN'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Joins in ABAP CDS Views'
define view Z_Cds_Joins as select from snwd_so
        right outer join snwd_so_i 
        on snwd_so.node_key = snwd_so_i.parent_key
{
    snwd_so.so_id, 
    snwd_so.billing_status,
    snwd_so.delivery_status,
    snwd_so.op_id,
    snwd_so_i.so_item_pos,    
    snwd_so_i.net_amount,
    snwd_so_i.currency_code
}

 

You have successfully learned how to use INNER JOIN in ABAP CDS Views on HANA. In the same way you can use left Outer Join and Right Outer Join.

Please stay tuned to us for more ABAP on HANA/ABAP in Eclipse tutorials. Please feel free to comment and let us know your feedback. You feedback will keep us alive.

Thank you.