Exploring ABAP on HANA [2] : Joins in CDS Views

In this post, you will learn to create CDS Views that contain joins. You will also learn about Right Outer Join and Cross Join which are recent additions to joins in ABAP.

Joins covered in this post are –

• Inner Joins
• Left Outer Join
• Right Outer Join
• Cross Join

The steps to create a CDS view are covered in post Exploring ABAP on HANA [1] : Introduction To CDS Views.

CDS View with INNER JOIN

Inner Join returns the entries meeting ON condition. The records are returned when corresponding records exist in both tables and if the table position is switched, the result is same.

To create a CDS View with Joins, below template can be used.

The template uses left outer join which you will need to change to inner join. Complete the code as below.

@AbapCatalog.sqlViewName: 'ZJP_CDS_JOIN'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS View with Inner Join'
define view zjp_cds_inner_join
  as select from spfli inner join scarr 
    on scarr.carrid = spfli.carrid
{ 
  key spfli.carrid      as id, 
  key scarr.carrname    as carrier, 
  key spfli.connid      as flight, 
      spfli.cityfrom    as departure,  
      spfli.cityto      as destination
}  

Output Sample

CDS View with LEFT OUTER JOIN

Left Outer Join returns all entries from left table even when there is no matching entry in right table. The fields from right table will be populated if matching entry is found, else the fields will be kept blank.

Example

@AbapCatalog.sqlViewName: 'ZJP_CDS_JOIN_L'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS View with Left Outer Join'
define view zjp_cds_left_join
  as select from scarr left outer join spfli
    on scarr.carrid = spfli.carrid
{ 
  key scarr.carrid      as id, 
  key scarr.carrname    as carrier, 
  key spfli.connid      as flight, 
      spfli.cityfrom    as departure,  
      spfli.cityto      as destination
} 

And the output

CDS View with RIGHT OUTER JOIN

Right Outer Join returns all entries from right table even when there is no matching entry in left table. The fields from left table will be populated if matching entry is found, else the fields will be kept blank.

The completed CDS view example is below.

@AbapCatalog.sqlViewName: 'ZJP_CDS_JOIN_R'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS View with Righ Outer Join'
define view zjp_cds_right_join
  as select from sflight right outer join spfli
    on  sflight.carrid = spfli.carrid
    and sflight.connid = spfli.connid
{ 
  key sflight.carrid    as id, 
  key sflight.connid    as flight, 
  key sflight.fldate    as departuredate,
      spfli.cityfrom    as departure,  
      spfli.cityto      as destination
} where spfli.carrid = 'DL'

Note that WHERE condition fis applied to the results set created using the join.

The output looks like below. (You might get different output based on entries in your system)

CDS View with CROSS JOIN

Cross Join returns the Cartesian product of rows from tables in the join i.e., it will produce rows which combine each row from the left table with each row from the right table. 

Example – Imagine a table with single field Color and another table with single field Shape. If we want to output all possible color and shape combinations – we can use cross join.

@AbapCatalog.sqlViewName: 'ZJP_CDS_JOIN_C'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Cross Join'
define view zjp_cds_cross_join
  as select from zjp_color
     cross join  zjp_shape
{
  key zjp_color.color as Color,
  key zjp_shape.shape as Shape
}

And the output

Before I end the post, few important points regarding joins

• Buffering should not be enabled for a CDS view with outer join.
• In nested joins, parentheses should be used explicitly to make the code readable.

Visit ABAP on HANA series for Tutorials on CDS, AMDP, Eclipse, and ALV IDA.

---

If you like the content, please subscribe…