Annotations, exposure of CDS Views as OData, Expressions & Operations, Built-In Functions, ADT Tools for ABAP CDS, Key Definitions in Core Data Services etc are some of the topic we have covered in details in SAPSPOT. 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
A. Difference between Associations and Joins:
Association is Lazy Join. Association is Join on Demand. Association (Exposed Association) is just the relationship and does not do the actual join until and unless someone triggers it. Ad-hoc Association and JOIN are same. Ad-hoc Association always does the JOIN. It is not lazy or on demand.
<code>// Exposed Association
define view ZMM_I_MAT as select from MARA as m
association to MARC as _plant on
m.mara = _plant.mara
{
key matnr,
_plant
}</code>In the above hypothetical code snippet, _plant is the Exposed association. Why Exposed?. Because, only the _plant is exposed. No fields are explicitly selected from _plant. The is no hard JOIN between MARA and MARC.
Take a note again. No field from _plant is in the list of output. But when user right clicks on output and goes to association, all the details of MARC would be displayed. This is on-demand or lazy join.
<code>// Ad-hoc Association
define view ZMM_I_MAT as select from MARA as m
association to MARC as _plant on
m.mara = _plant.mara
{
key matnr,
key werks,
_plant.mmsta
}</code>In the above code snippet, _plant.mmsta is always displayed in the output. That means, there is a real JOIN at all times between MARA and MARC. This is Ad-hoc Association. Ad-hoc Association and JOIN do not have much difference.
With the above bites, let us start understanding in more conventional way what Associations are and how they are different from Joins.
Associations define relationship between data model entities and not just join data sources as in Joins. Its syntax keeps the relation between data model entities visible and not concealed as in join conditions. However, there is no difference between associations and joins at database level. Eventually, every association is translated into join condition at database level.
But with exposed associations (which we will cover in detail later), the join is executed only when the consumer requests data from the associated data source. It is also referred as “JOIN on Demand” and can help improve performance.
Example of JOIN in CDS View
Example of using Association in place of Join
In the above example, three points are highlighted –
- The two FROM clauses differ in the use of keywords “Association to” and “Left Outer Join”. ON Condition remains same in both cases.
- There is also small difference in element list. In the field list of view with association, all the fields without explicit source are taken from the primary data source (table SPFLI in this case).
In the element list of Join, data source of field (i.e. SPFLI.CARRID, SPFLI.CONNID) is mandatory in case of non-unique field names. Thus, in above example of Join, it will throw a syntax error if data source is not mentioned for fields CARRID and CONNID as both the field names are present in both the JOIN tables.
- For all the fields from associated table, it is mandatory to specify the data source (field SFLIGHT.FLDATE in this example) however with JOINS it is only needed 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.
Association Name and not an Alias
While defining an association we can use addition AS to specify alias. Its syntax is very similar to the definition of aliases in the join. But strictly speaking, it is not an alias for the data source but a name of the association. Confused???? Let us take an example to understand this in a better way.
In the example above, we have made use of AS for the associated table SFLIGHT. By just looking at the syntax, it looks like an alias for the table SFLIGHT. However, it is not an alias at the database level but the name for the association.
The difference becomes clear when we look at the SQL Create statement of the above view definition.
In the SQL create statement, you can see alias for table SFLIGHT generated is not _SFLIGHT as defined in view definition but a more generic alias “A0”. This means association name _SFLIGHT is only a semantic information.
Note: It is recommended by SAP, but not a fixed rule, that names for associations begin with character “_” followed by meaningful name for the association to further improve the readability of the view definition.
B. Cardinality in Associations
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 Association is optional. If the cardinality is not explicitly defined, the cardinality “0 ..1” is implicitly used.
Some of the rules regarding the usage of cardinality are:
- Cardinality is optional. By default it is “0..1”
- Default value for minimum is 0.
[1] means [0 ..1]
[3] means [0 ..3]
[] means [0 ..]
- Minimum cannot be * and maximum cannot be 0.
- Syntax check for cardinality having maximum > 1 as follows –
- Syntax error “Value set associations are not allowed 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 <> can influence the cardinality of the resulting set” if the association field is used in the element list.
Let us take example of the above two syntax check warning/errors.
- 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.
How Cardinality Alters the Behaviour of SELECT Query in Reports?
Let us see how the cardinality of associations affect the Select Query accessing the CDS view.
In our example we have considered table SPFLI as source table and SFLIGHT as associated table. For purpose of better understanding we will restrict the data to CARRID = ‘AA’ and CONNID = ‘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.
1. 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.
Output –
- 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.
Output –
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 be used in the field element.
Example –
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.
