1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
Products
Products Industries
Industries Support
Support Training
Training Community
Community Developer
Developer
Partner
Partner About
About
Ask a Question Write a Blog Post Login
Horst Keller
August 12, 2016 3 minute read
From Open SQL Joins to CDS Associations
Follow RSS feed
4 Likes 10,409 Views 21 Comments
In this short blog I will use the most primitive example to show you the way from joins in
ABAP Open SQL to associations in ABAP CDS.
The aim of the blog is not to show you something you should do but to gain a basic
understanding of associations in CDS views.
Step 1, Join in Open SQL
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 1/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
I will start with the following very simple INNER JOIN between database tables SPFLI and
SCARR from the good ol’ ight model in Open SQL in the ABAP Editor (either WB or ADT in
Eclipse):
SELECT FROM spfli
INNER JOIN scarr ON
spfli~carrid = scarr~carrid
FIELDS scarr~carrname AS carrier,
spfli~connid AS flight,
spfli~cityfrom AS departure,
spfli~cityto AS arrival
ORDER BY carrier, flight
INTO TABLE @DATA(result_open_sql_join).
Nothing special about that and the result shown with CL_DEMO_OUTPUT looks as follows:
Step 2, Join in ABAP CDS
Now let’s transform the above ABAP code into the DDL of an ABAP CDS view in the DDL
source code editor of ADT in Eclipse:
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 2/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
@AbapCatalog.sqlViewName: 'DEMO_CDS_JN1'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_join1
as select from spfli
inner join scarr on
spfli.carrid = scarr.carrid
{
scarr.carrname as carrier,
spfli.connid as flight,
spfli.cityfrom as departure,
spfli.cityto as arrival
}
This can almost be done by copy and paste. Hey, it’s (almost standard) SQL for both.
After activating this view you can use the data preview of ADT (F8) or acces it with Open
SQL:
SELECT FROM demo_cds_join1
FIELDS *
ORDER BY carrier, flight
INTO TABLE @DATA(result_cds_join).
It is not too surprising that result_cds_join and result_open_sql_join contain
exactly the same data.
Step 3, Association in ABAP CDS
Finally, I will use the advanced modelling capabilty of ABAP CDS and transform the explicit join
into an association of another view:
@AbapCatalog.sqlViewName: 'DEMO_CDS_JN2'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_join2
as select from spfli
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 3/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
association to scarr as _scarr on
spfli.carrid = _scarr.carrid
{
_scarr[inner].carrname as carrier,
spfli.connid as flight,
spfli.cityfrom as departure,
spfli.cityto as arrival
}
The association _scarr is declared once behind the keyword association and can be used
at several places inside the view in path expressions. You can also publish it for usage in other
views or in Open SQL, but I have not done that here.
For our simple example, I use the path expression _scarr[inner].carrname as the rst
element of the select list. When using a path expression, the associations listed there are
internally transformed to joins. In the select list those joins are left outer joins by default.
Therefore, I override the default with [inner] in order to enforce an inner join. You can check
the result by displaying the SQL DDL (shown for HANA here) that is generated from the ABAP
CDS DDL in ADT (Context menu Show SQL CREATE statement):
CREATE VIEW "DEMO_CDS_JN2" AS SELECT
"SPFLI"."MANDT" AS "MANDT",
"=A0"."CARRNAME" AS "CARRIER",
"SPFLI"."CONNID" AS "FLIGHT",
"SPFLI"."CITYFROM" AS "DEPARTURE",
"SPFLI"."CITYTO" AS "ARRIVAL"
FROM "SPFLI" "SPFLI" INNER JOIN "SCARR" "=A0" ON (
"SPFLI"."MANDT" = "=A0"."MANDT" AND
"SPFLI"."CARRID" = "=A0"."CARRID" )
You see, we end up with something well known.
And of course, the data preview of ADT (F8) or the following Open SQL retrieve again the same
data as before:
SELECT FROM demo_cds_join2
FIELDS *
ORDER BY carrier, flight
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 4/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
INTO TABLE @DATA(result_cds_assoc).
With other words, no exceptions from
ASSERT result_cds_join = result_open_sql_join.
ASSERT result_cds_assoc = result_cds_join.
Conclusion
The aim of this simple example is to show you that CDS associations are nothing but
speci cations of joins in a central position. These joins are instantiated in native SQL
when using associations in path expressions.
The bene ts of using associations are not shown in the simple example here. The advanced
modelling capabilities stem from the reuse of the associations (meaning their joins) in di erent
positions. Of course, path expressions can contain more than one association, relieving you
from the task of coding complex join expressions. Such path expressions can be used in the
same or other CDS views and even in Open SQL (if published by the de ning view).
Like Alert Moderator
Assigned tags
ABAP Development | abap | cds |
Related Blogs
Core Data Services in ABAP
By Former Member , Feb 22, 2016
Performance of ABAP CDS on SAP HANA at SAP TechEd 2017
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 5/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
By Randolf Eilenberger , Mar 02, 2018
ABAP new Open SQL and CDS runtime
By Former Member , Sep 09, 2016
Related Questions
Open SQL and CDS view bu er
By Eric Yu , Dec 18, 2017
CDS Associations with lters
By Former Member , May 30, 2017
Limitations of Associations in CDS views
By Former Member , Aug 28, 2017
21 Comments
You must be Logged on to comment or reply to a post.
Abdul Hakim
August 12, 2016 at 2:26 pm
Good one.
Like (0)
Jelena Per ljeva
August 12, 2016 at 3:51 pm
Horst, I was just going to suggest to add a bit more introduction (like why would anyone
want to do this). Based on a quick Google search, it seems you’ve missed the opportunity
to pimp your other related blogs:
CDS – One Concept, Two Flavors
ABAP News for 7.40, SP08 – ABAP Core Data Services (CDS)
Like (0)
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 6/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
Horst Keller | Post author
August 12, 2016 at 5:15 pm
like why would anyone want to do this
SAP’s next generation ABAP applications as S/4 HANA will be built on 10000s of CDS
views interconnected by associations. Sometime in the future you will have to deal with
those in custom code and then it might be good to know that it is all about joins.
Like (0)
Sandra Rossi
August 13, 2016 at 8:28 am
My dream: that would be so good if CDS views with associations could be provided by SAP
in the classic non-S/4 HANA systems! (they could be used by customers as a technical
data model, it would be much easier to nd the relationships between tables, much better
than the old foreign keys and EDM)
Like (0)
Horst Keller | Post author
August 13, 2016 at 8:45 am
Yep, that’s the idea. While semantical properties of database tables as foreign key
relationships never had any technical meaning in ABAP (only in dynpros), the modelling
capabilities of CDS do have real e ects.
Like (1)
Former Member
October 27, 2016 at 1:20 pm
You can also navigate the public associations in the Data Preview in ADT (ABAP in Eclipse).
See Data Preview on ABAP CDS in AiE .
Like (0)
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 7/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
Amisha parikh
December 14, 2016 at 1:02 pm
I have question. I created one view and included for e.g. 10 elds in projection list
(ZCDS1). Now I want to create other cds view(ZCDS2) using ZCDS1 and sap table1 using
join or association . I want to use all the elds of ZCDS1 + new table two elds.
Every time I have to add all 10 elds in projection list from zcds1 manually . Is there any
way to select all the elds without typing all the elds of ZCDS1 eld list ?
Because if I add new led in ZCDS1 I have to again change in ZCDs2.
Like (0)
Horst Keller | Post author
December 14, 2016 at 4:13 pm
Sorry, no.
You can do so in Open SQL (dbtab~*), but not yet in ABAP CDS.
Like (0)
Alexander K
December 15, 2016 at 3:20 am
Hello, Horst.
Can you give an example, in which one association relieving you from the task of coding
complex join expressions.
Why association is more convenient then join?
Like (0)
Horst Keller | Post author
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 8/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
December 15, 2016 at 5:58 pm
Associations model relations between entities. Once modelled, the user can use path
expression to naturally follow such relations, e.g. Open SQL:
SELECT DISTINCT carrname
FROM demo_cds_assoc_sairport_utc1\_spfli\_scarr AS scarr
ORDER BY carrname
INTO TABLE @DATA(result).
Here, two associations of two views are used in one path expression.
Example taken from docu
Like (1)
Alexander K
December 16, 2016 at 4:15 am
hanks, Horst.
Now I understood the main advantage associations over the joins.
This is path expressions.
Like (0)
Horst Keller | Post author
December 16, 2016 at 6:30 am
Yep
Like (0)
Ceyhun Alp
April 18, 2017 at 7:58 pm
Hello Horst and everyone,
Thank you for this great blog, at rst. Very useful and helpful .
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 9/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
I got stuck somewhere and trying to nd the answer of the questions that makes my mind
busy for a while. I guess, association doesn’t have all the features join have or it has some
other use cases than complex joins.
When I try to make 2 or more associations and use the elds in the rst associated view to
join the new associations, things start to get mixed. Better to give an example;
@AbapCatalog.sqlViewName: 'ZdemoCdsJn2 '
@EndUserText.label: 'CDS Demo Join'
define view Zdemo_Cds_Jn2
as select from spfli
association to scarr as _scarr on
spfli.carrid = _scarr.carrid
association to scarplan as _scplan on
$projection.carrid= _scplan.carrid
{
key spfli.carrid as id,
key spfli.connid as flight,
spfli.cityfrom as departure,
spfli.cityto as arrival,
_scarr.carrid,
_scplan.planetype
}
The complier gives the following error
“Association _scplan cannot be used locally within the view”
Actually, my goal is also associating SPLANE table on the eld _scplan-planetype. But it
doesn’t let to use this eld.
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 10/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
What does the “Association _scplan cannot be used locally within the view” error mean?
So, that means we can use association only for spesi c scenarios? For navigating with
Odata etc? But for complex scenarios, it looks like join has more features.
Thank you very much for all your cooperation,
Ceyhun
Like (0)
Horst Keller | Post author
April 19, 2017 at 6:37 am
You have a kind of recursion. You use $projection that refers to the SELECT list and use the
association in the SELECT list itself. The following is error free:
define view ...
as select from
spfli
association to scarr as _scarr on
spfli.carrid = _scarr.carrid
association to scarplan as _scplan on
spfli.carrid = _scplan.carrid
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 11/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
{
key spfli.carrid as id,
key spfli.connid as flight,
spfli.cityfrom as departure,
spfli.cityto as arrival,
_scarr.carrid,
_scplan.planetype
}
Like (1)
Ceyhun Alp
April 19, 2017 at 7:23 am
Thank you.
As I see when we use the base datasource table (here sp i) it works ok. Actually what i
need is to use an associated view (let’s say _scarr) in another association and use the
elds of the nal associated table in my cds view.
When i use traditional join, it looks ok. But my main goal is to create an analytical model
with associations. To do this, i guess i need to understand the association concept rst.
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 12/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
Thank you,
Ceyhun
Like (0)
Former Member
July 29, 2017 at 10:44 am
Hi Horst,
I was facing the same issue, but eld required for the on condition(BSEG-LIFNR) for the
second association is not available in the initial data source BKPF for me.
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 13/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
I understand from your reply is that we can not use elds from the selection list in the ON
condition which themselves point to another association if we want the elds from the
association to be included in the selection list for the current view.
But I have no other option here as LIFNR is not available here in BKPF, else I would have
used it from there.
I was able to resolve this issue by changing the tables, and making BSEG as my rst
table.Wanted to check if we can activate the CDS view without changing the order of the
tables?
If I remove the path expression _lfa1.name and replace it with _lfa1 so that it is exposed to
consumption by other views, then the error goes but we get two warnings.
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 14/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
Could you please comment on the warnings as well. Thanks for your help!
Warm Regards,
Raveesh
Like (0)
Jakob Mainka
January 18, 2018 at 1:40 pm
Hello,
I’m desperately searching for a possibility, to build and ll a table mesh straight away from
my CDS view incorporating the associations.
In the the whole Structure looks like:
[Workcenters]
-[Tasks Lists of Workcenters]
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 15/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
–[Materials of Task Lists]
—[Consumption of Materials] (Transposed from columns to rows)
—-[Workdays per Month] (Transposed form columns to rows)
With of course some additional calculation in between
As now based on this path structure, I’d like to create an ABAP report
Unfortunately (as of now) I can’t nd anything?
Any help would be appreciated
Like (0)
Horst Keller | Post author
January 18, 2018 at 5:05 pm
“I’m desperately searching for a possibility, to build and ll a table mesh straight away
from my CDS view incorporating the associations.”
Me too …
Like (1)
Jakob Mainka
January 19, 2018 at 10:06 am
Then let’s hope and pray
But now I have another issue with Associations compared to Joins…
I have a view (C_MAT_CONS) an association (on WERKS and MATNR) from material
(B_MAT) to material consumption (B_CONS)…
In this view I have a SUM on the consumption grouped by WERKS and MATNR.
Sure when going to the preview, it calculates based on the WHOLE consumption…
Now when I select the data via ABAP and add a where condition which “ lters” the
consumption data, this restriction is not considered by the SUM function on the view!
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 16/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
But when I write a simple JOIN between B_MAT and B_CONS again only joined via WERKS
and MATNR grouped and summarized, the SUM is based on my where condition on
B_CONS
Same if i select from the VIEW and calculate the SUM within my select query, again it
calculates properly!
So only the VIEW itself is not considering my where restriction to the association!?
Like (0)
Ravichand S
May 16, 2018 at 12:52 pm
Hi Horst Keller
I created a association view like below.
@AbapCatalog.sqlViewName: ‘ZS4HCDS_ASS04’
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘Filtered Associations: cardinality “to-N”’
de ne view ZS4HCDS_ASS_04
as select from scustom as customer
association [0..*] to sbook as _bookings on customer.id = _bookings.customid
{
customer.id,
customer.name,
//Exposed Association with lter
_bookings[class=’C’] as _business_ ights,
//Exposed Association without lter
_bookings
and i used this association view in my program like below.
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 17/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
SPAN {
font-family: “Consolas”;
font-size: 10pt;
color: #000000;
background: #FFFFFF;
}
.L0S31 {
font-style: italic;
color: #808080;
}
.L0S52 {
color: #0000FF;
}
.L0S55 {
color: #800080;
}
.L0S70 {
color: #808080;
}
*&———————————————————————*
*& Report ZS4HCDS_ASS04_CONSUME
*&———————————————————————*
*&
*&———————————————————————*
REPORT zs4hcds_ass04_consume.
**– Association without Filter.
SELECT FROM zs4hcds_ass04
FIELDS \_bookings–mandt AS mandt , \_bookings–carrid AS carrid , \_bookings–
connid AS connid
INTO TABLE @DATA(lt_2).
but i’m getting the sytax error like “The association “_BOOKINGS” could not be
resolved.”
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 18/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
Thanks
Ravichand
Like (1)
ERALPER YILMAZ
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 19/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
November 1, 2018 at 12:09 pm
I experienced the same error message when I try to use new Open SQL syntax for elds in
SELECT list from associations
Horst Keller is it possible for you to check where we are making a mistake
For example my test view as follows
@AbapCatalog.sqlViewName: 'ZEY_ORDER_CDS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Test CDS View'
define view ZEY_Order
as
select from vbap as I
association [0..1] to SEPM_I_Currency as _Currency
on $projection.waerk = _Currency.Currency
{
I.vbeln,
I.posnr,
I.netwr,
I.waerk,
_Currency.Currency,
_Currency
}
And the ABAP code to consume it as follows
SELECT
FROM zey_order_cds
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 20/21
1/29/2019 From Open SQL Joins to CDS Associations | SAP Blogs
FIELDS
vbeln,
\_Currency-CurrencyISOCode
INTO TABLE @DATA(lt_vbap)
UP TO 10 ROWS.
And the error message I got is
The association “_CURRENCY” could not be resolved.
Like (0)
Share & Follow
Privacy Terms of Use
Legal Disclosure Copyright
Trademark Cookie Preferences
Sitemap Newsletter
https://blogs.sap.com/2016/08/12/from-open-sql-joins-to-cds-associations/ 21/21
