Skip to Content
Author's profile photo Ashish Trivedi

Associations in ABAP CDS(7.40)

Hello All,

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.

Regards,

Ashish T.

 

 

 

 

 

 

 

 

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo sas vas
      sas vas

      Ashish can I speak to you regarding one old requirement where you have worked on !! Let me know how to connect thanks in advance.

      Author's profile photo Supriya Sahu
      Supriya Sahu

      Hi Ashish,

      Can you please tell me how do we get the  create statement screen to check for joins ?

       

      Thanks,

      Supriya

      Author's profile photo Ashish Trivedi
      Ashish Trivedi
      Blog Post Author

      Sorry for late ( its very late actually) reply. That we can see if you have HANA  DB access. then inside your schema ->under View you can find your view.