(opens in a new tab)"/>Core Data Servicesetc are some of the topic we have covered in details in SAPYard. But, when it comes to Associations in CDS views, developers are still not that comfortable as they are with other topics of CDS.
Since Associations is a such a subtle subject which demands more respect than just a mention here and there. Therefore we have divided the Associations concept into three parts, trying to cover it in as much detail as we can.
After completing all the lessons, developers would be able to –
- Understand the difference between Associations and Joins
- Define Views with Associations
- Understand Cardinality in Associations
- Understand and use exposed Associations
- Use exposed Associations in path expressions
- Understand filtered Associations and Cardinality in filtered Associations
- Understand the use of Annotation @CompareFilter
Also Read : ADT Eclipse/HANA Studio for ABAPers
Defining Associations in CDS Views & its Cardinality would be the ideal sub-title for this article.
Let us start with understanding what Associations are and how they are different from Joins.
Associations definerelationship between data model entities and not just join data sources asin Joins. Its syntax keeps the relationbetween data model entities visible and not concealed as in join conditions.However, there is no difference between associations and joins at database level. Eventually, everyassociation is translated into join condition at database level.
But with exposed associations (which we will cover in detail later), the join is executed onlywhen the consumer requests data from the associated data source. It is alsoreferred as “JOIN on Demand” and canhelp improve performance.
Example of JOIN in CDS View
Example of using Association in place of Join
In the above example, three points are highlighted –
- Thetwo FROM clauses differ in the use of keywords “Association to” and “Left OuterJoin”. ON Condition remains same in both cases.
- Thereis also small difference in element list. In the field list of view with
association, all the fields withoutexplicit source are taken from the primary data source (table SPFLI in thiscase).
In the element list of Join, data sourceof field (i.e. SPFLI.CARRID, SPFLI.CONNID) is mandatory in case of non-uniquefield names. Thus, in above example of Join, it will throw a syntax error ifdata source is not mentioned for fields CARRID and CONNID as both the fieldnames are present in both the JOIN tables.
- Forall the fields from associated table, it is mandatory to specify the datasource (field SFLIGHT.FLDATE in this example) however with JOINS it is onlyneeded in case of non-unique fields.
Association and its corresponding SQL CREATE statement
The SQL CREATE statement of view definition above reveals, that on database level, it is nothing more than a join definition.
Also Check – How I created my first OData Service in SAP?
Association Name and not an Alias
While defining an association we can use addition AS to specify alias. Its syntax is verysimilar to the definition of aliases in the join. But strictly speaking, it is not an alias for the data source but aname of the association. Confused???? Let us take an example to understand thisin a better way.
In the example above, we have made use of AS for the associatedtable SFLIGHT. By just looking at the syntax, it looks like an alias for the table SFLIGHT. However, it is not analias at the database level but the namefor the association.
The difference becomes clear when we look at the SQLCreate statement of the above view definition.
In the SQL create statement, you can see alias for tableSFLIGHT generated is not _SFLIGHT as defined in view definition but a moregeneric alias “A0”. This meansassociation name _SFLIGHT is only a semantic information.
Note: Itis recommended by SAP, but not a fixed rule, that names for associations begin with character “_” followed by meaningfulname for the association to further improve the readability of the viewdefinition.
Cardinality is the relationship between the source and associated table (or CDS view) included in the definition of the association in the form of [MIN .. MAX] (only the target cardinality is stated). It is mainly used to document the semantics of the data model. It is not validated at the runtime but can produce syntax check warnings.
Note: Cardinality is defined only for the Target (associated) Table/View. [0..1] = It means the cardinality of target can be 0 or 1. Similarly [0..N] means the target table/view cardinality can be from 0 to N.
Specifying the cardinality after the keyword Associationis optional. If the cardinality is notexplicitly defined, the cardinality “0 ..1” is implicitly used.
Some of the rules regarding the usage of cardinality are:
- Cardinalityis optional. By default it is “0..1”
- Defaultvalue for minimum is 0.
means [0 ..1]
means [0 ..3]
[*]means [0 ..*]
- Minimum cannot be * and maximum cannotbe 0.
- Syntaxcheck for cardinality having maximum > 1 as follows –
- Syntax error “Value set associations are notallowed here” is issued if association is used in where clause.
Also,it is not possible to add a field of associated view (or table) in EXTEND VIEW.
- Syntax warning “Association <> caninfluence the cardinality of the resulting set” if the association field isused in the element list.
Let us take example of the above two syntax checkwarning/errors.
1. Syntax error “Value set associations are not allowed here” is issued if associated table (SFLIGHT in this case) is used in WHERE clause if cardinality is > 1 as shown below-
2. Syntax warning “Association <> can influence the cardinality of the resulting set” if the association field is used in the element list.
Also Explore : How to Consume Custom OData in SAPUI5 Application
How Cardinality Alters the Behaviour of SELECT Query in Reports?
Let us see how thecardinality of associations affect the Select Query accessing the CDS view.
In our example we haveconsidered table SPFLI as source table and SFLIGHT as associated table. Forpurpose of better understanding we will restrict the data to CARRID = ‘AA’ andCONNID = ‘17’
TABLE – SPFLI
TABLE – SFLIGHT
As we can see the relation between table SPFLI to SFLIGHT is 1: many i.e. one entry of SPFLI can have multiple entries in SFLIGHT table.
- When cardinality is [0..1] or [1..1] in Associated Table SFLIGHT
In the above CDS View, cardinality [0..1] is used in association (means – associated table SFLIGHT can have 0 or 1 entry for source table SPFLI). So, when no attributes from the associated table SFLIGHT is used in SELECT statement, JOIN to table SFLIGHT does not happen and table SFLIGHT is not accessed internally.
The relationship between table SPFLI and SFLIGHT is 1 to many. But when no fields from SFLIGHT table is selected in the Report program which consumes the CDS View, JOIN does not happen in the result set of the SELECT statement, and hence records from SPFLI are not duplicated. This behaviour is noly for cardinality [0..1] and [1..1].
So, we can say, Cardinality [0..1] internally works in the same way as LEFT OUTER TO ONE JOIN.
In the above SELECT query in the report program, only the fields of table SPFLI are accessed in the CDS View. CARRID, CONNID and DISTANCE are fields of SPFLI. Cardinality is [0..1], which means the associated table (SFLIGHT) can have 0 entries or 1 entries depending on how the CDS is consumed in the Report.
For the above example Report, since SFLIGHT.FLDATE is not being picked in the SELECT statement from the CDS ZTRNX_CDS_DEMO11, JOIN does not happen between SPFLI and SFLIGHT in the CDS View and only table SPFLI is accessed internally. Thus, records are not duplicated.
- When cardinality is [0..*] or [1..*]for the Associated Table SFLIGHT
In the above CDS View, cardinality [0..*] is used in association. So, even when no attributes from the associated table SFLIGHT is used in SELECT statement (in Reports or other programs where the CDS View is consumed), JOIN to table SFLIGHT happens and table SFLIGHT is also accessed internally.
Cardinality can alter the behaviour of the query even when the SELECT statement is exactly the same.
The relationship between table SPFLI and SFLIGHT is 1 to many, and when cardinality of associated table SFLIGHT is set as [0..*] or [1..*] then JOIN happens in the result set of the SELECT statement even if the fields of SFLIGHT are not SELECTED. Thus records from SPFLI are duplicated.
So, we can say, Cardinality [0..*] and [1..*] internally works in the same way as LEFT OUTER JOIN.
With the same SELECT Query, as cardinality is [0..*], JOIN does happen (even though SFLIGHT fields are not fetched in the SELECT Query) and both the tables SPFLI and SFLIGHT are accessed internally. Thus, records are duplicated.
2572 * 15 = 38580.0 .. ?
By default, an association works like LEFT OUTER JOIN or LEFT OUTER TO ONE JOIN internally.
Note : Same SELECT in the report but with different Cardinality, first [0..1] and for second [0..*], the Output Result varies.
Keyword [inner] in Association
If INNER JOIN needs to be used, keyword [inner] should beused in the field element.
Corresponding Create SQL statement –
Let us stop here today. The fundamentals explained in this article would be the foundation stone for clarity of CDS View as a whole. Please go through the article one more time if needed. Try to create the CDS View in your system. Play around with the Cardinality. Please note that you do not need to be in HANA or S/4HANA to practice CDS. ABAP CDS can be practiced in ECC as well provided you have the right Service Pack.
Take special note of the Cardinality. Remember, Cardinality changes the output of the same exact SELECT query.
In the 2nd part of this series, we wouldexplore more on Associations and show you some tricky stuff.
So, please stay tuned. If you think our page is helpful, do not forget to share our links with your friends, colleagues and team members. Do like our facebook page and follow us at twitter and instagram.
Your feedback is most welcome. Please, please leave your comments below.
We have a very active Telegram (App) SAP Technical Group with more than 3880+ SAP Technical Practitioners from 6 Continents of the SAP World. Please join it using below link.
Telegram SAP Technical Discuss Group. You need to install the Telegram App first on your mobile device. Once you have it on your mobile, you can join the group and also access it from the Web on your computer and laptop.
Free Step by Step Core Data Services Exercises
- CDS Part 1. Core Data Services – Introduction
- CDS Part 2. Core Data Services – Deep Dive
- ABAP on SAP HANA. Part XII. Open SQL, CDS or AMDP, which Code to Data Technique to use?
- CDS Part 3. Expose CDS Views as OData Service through Annotation
- CDS Part 4. Data Access in S/4HANA Cloud – CDS View Introduction
- CDS Part 5. ABAP CDS Views With Authorization
- CDS Part 6. Basic Expressions & Operations Available for CDS View – I
- CDS Part 7. Basic Expressions & Operations Available for CDS View – II
- CDS Part 8. Usage of Built-In Functions in CDS – I
- CDS Part 9. Usage of Built-In Functions in CDS – II
- CDS Part 10. Usage of Built-In Functions in CDS – III
- CDS Part 11. How to Consume CDS View in Smart Business Service KPI Fiori Apps?
- CDS Part 12. Useful 6 CDS Related Tools in ADT
- CDS Part 13. Key Definition in CDS Views
- CDS Part 14. ABAP Annotations for Translatable Texts in CDS Views
- CDS Part 15. Associations in CDS Views – I