CDS View: All About Different Joins

Inner Join/Left Outer join/Right Outer Join/Cross Join

---

Below post shows about different joins in CDS view where we take example of SCARR and SPFLI table.

Here below entries present currently in DB tables SCARR and SPFLI.

---

Below CDS view shoes Inner Join which simply can be called as join between SCARR & SPFLI tables. It has JOIN Condition which is specified with keyword- ON 

It gets the matching records from two tables. The cardinality between SCARR & SPFLI is [1..N] .

Make the data preview:  It brings all matching records .

---

The below CDS view makes Left Outer Join between the two data sources.

The graphical view. It Picks everything from left table and if matching found from right table then the corresponding FIELD values are filled else left blank.

Make data preview. CONNID fields selected from SPFLI ut when not found set as 0000 and so other SPFLI fields left blank.

The COALESCE function can be used which takes two arguments , first argument is returned if not initial else the seccod argument is returned.  In below CDS view this function can be sued, in case we have empty value we can set this fields value something else.

Make preview and now below, countryfr and countryto column values are filled with test ‘No value’ when right side entry not found. If the right side entry found but these two field values are not filled then this function dont work for them.

---

Below CDS is having a right outer join .

Graphical Editor details.

Data preview.

---

The CROSS JOIN in CDS view where ON condition is not required but in all other above 3 cases ON condition required. As cross join is a cartesian product let see the result of the CDS.

SCARR table contains 18 records and SPFLI contains 2 records with CARRID = AA .

So resulting 18*2 = 36 rows as CDS output selection list.

---