CDS View Association – Part-1

CDS View  with Association [1..1] and use of path expression 

---

This post shows how to create a association between two data sources and its cardinality and how to expose the association data source and consuming the CDS view from ABAP program requesting fields of the main data source/association data source.

An association  in a CDS view associates source data source with a target data source. The cardinality [min . . max]  defines the cardinality of the target data source. 

min – can be omitted then assumed as 0 , min can not be * .

max- cannot be 0, max can be * means any no of rows.

When SQL SELECT  query is performed on a CDS view  that uses a path expression to access an association or simply the path expression  is used, then the association is transformed into a join expression. 

---

Let’s create a CDS view with association with source data source as – SPFLI and target data source as – SCARR and these source & target source have cardinality as [1. . 1].

The select attributes include 4 fields from the source data source and also 1 fields from the association i.e carrname. Save and activate the cds view.

In SE11 , the corresponding SQL view also includes the CARRNAME from association table-SCARR.

And the SQL view contains a join condition like CDS view with JOIN. This is because though it is a CDS view with association but one of the element CARRNAME is included in the selection list.

Here we have the graphical editor.

The underlying SPFLI table records.

Make Data preview. 

As there is no association is exposed, we don’t find any list of associations.

Here we can have a select query on the CDS view with association.

---

Let’s make the association field CARRNAME not a part of the selection list. 

The SQL view.

There is no join and the selection list don’t include any field of the association.

Data preview.

---

Let’s make thing little more good, now in the CDS view let’s expose the entire association but don’t include any association field in the select list. 

No join condition in the SQl view if only the association is exposed but no field is of the association is included in the selection list.

Graphical editor view.

Make data preview. Here no join condition is evaluated.

Now as we have exposed the association [ _SCARR] , select any record and we can navigate to the association.

Now the ON condition is evaluated and we can see the details of the association data source.

This is how the SQL query is made on the CDS view to request the details of the association data source. It uses the path expression [ \ ] backslash and [ – ] operator.

---

Now make a select query on the CDS view and we get all the source fields of the view. No association field requested , so no join condition evalauted.

As the complete association is exposed, we can make request for specific fields as shown below. This time the ON condition is evaluated.  The result contains 5 fields, 4 from the source and 1 from the association.

---

Here we can request for multiple association fields and also in WHERE condition the path expression can be maintained for the association fields.

---

We can also request only the fields of the association data source  with different values in WHERE condition.

Last but not least also DISTINCT gives now duplicate records. 

---