Associations in ABAP CDS(7.40)

I would like to present one simple example to explain Association in ABAP CDS view in 7.40 environment.  In this example i will not concentrate on theory we will just take 3 tables and try to show association.

Let us take one problem statement

Problem Statement– “Need the details of all employees having valid addresses in database either PERMANENT or TEMPORARY”

We have 3 tables-

Z222422_EMP_BASE

Base table of an employee records, contains records as follows:

Z222422_EMP_ORG– contains employee org details

Org data table contains below entries

Z222422_EMP_ADD– contains employee address info

table contains entries:

We can achieve this by join statement. Left outer join on all table will cater the result.

(Base table and Org table will have 1 to 1 cardinality as employee must have org data)

Lets us see how these statements have been converted in DB (As we can expect as we have put left outer join in query)

Now let’s start same thing with Association :

First we will fetch employee and Org details. Create one CDS view with association.

Select list contains 3 fields and association name.

Data Source-  Here data source is Z222422_EMP_BASE and target data source is Z222422_EMP_ORG. Join Key condition can be seen after ON.

Cardinality-  its defined in square bracket. 1..1 indicates for 1 employee there will be 1 org data. Or 1 Employee belongs to 1 org data .

0..1 – indicates =  can have 0 rows in source data source and 1 row in target data source.  Employee can belong to 0 or 1 org data. 0..1 can be written as 1

0..* – indicates –  Employee can belong to 0 or any number of Org data

Last statement exposes the association for other views, if we don’t expose association and try to use in other view, it gives syntax error in that view. Declaring in Select list is like “Make it Public” its not Private.

If we check quick outline it gets displayed like below (Right click and select Quick outline)

We can use association elements in same view also, but in that case if we expose association, warning will be issued –“ Use of the association _BASE_ORG can lead to cyclical dependencies”

Association gets converted into join when they encounter path expressions.

Lets see when _BASE_ORG.designation has been used in select list

SQL create statement

Now let’s check create statement when _BASE_ORG is exposed but no path expression has been used, means we are not using association element in the same view

See the difference in the statements.

Execution of ZCDSV_BASE_ORG shows all rows of BASE table

 Now it’s time to model second view. This view uses the first view as target data source

If we check create statement –

Execute

Final View Modelling:

Now we have two views those are reading data from DB tables. We will use path expressions in final view

Just we need to connect with dots and we can see the association like below

Create statement

We can see associations are converted into outer Joins. By Default it is left outer join always.

If we want to add any condition in final view we can add as below:

Problem statement- Fetch address of only designation = SSE

Here we have given condition inside view- in path expression, that is the power of Association. We need not to know the joins/data source of initial views.

Execute

See how create statement gets changed

We can use path expressions in Where clause also. IN ABAP 7.5 we can consume Associations in our beautiful ABAP report also. I hope this example will help a little bit for beginner.