Monday 24 July 2017

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

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

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

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

Z222422_EMP_ORG– contains employee org details

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

Org data table contains below entries

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

Z222422_EMP_ADD– contains employee address info

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

table contains entries:

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

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)

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

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

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

Now let’s start same thing with Association :

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

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

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

ABAP CDS Tutorials and Materials, SAP ABAP Certifications
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.

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

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

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

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”

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

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

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

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

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

See the difference in the statements.

Execution of ZCDSV_BASE_ORG shows all rows of BASE table

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

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

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

If we check create statement –

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

Execute

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

Final View Modelling:

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

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

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

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

Create statement

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

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

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

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
ABAP CDS Tutorials and Materials, SAP ABAP Certifications

See how create statement gets changed

ABAP CDS Tutorials and Materials, SAP ABAP Certifications

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.

2 comments:

  1. Thank you for sharing the information here. Its much informative and really i got some valid information. You had posted the amazing article.

    ABAP Training in Chennai

    SAP FICO Training in Chennai

    ReplyDelete
  2. Awesome explanation. Your articles are very informative and useful to every ABAPer. Thank you and keep sharing :)

    Can you please post an article on how to use assocations in ABAP 7.5 report.

    ReplyDelete