Get Example source ABAP code based on a different SAP table
• [ ] ABAP_CDS_SELECT_ASSO • OF EXACT ONE TO EXACT ONE ABAP_CDS_SELECT_ASSO • OF EXACT ONE TO MANY ABAP_CDS_SELECT_ASSO • OF EXACT ONE TO ONE ABAP_CDS_SELECT_ASSO • OF MANY TO EXACT ONE ABAP_CDS_SELECT_ASSO • OF MANY TO MANY ABAP_CDS_SELECT_ASSO • OF MANY TO ONE ABAP_CDS_SELECT_ASSO • OF ONE TO EXACT ONE ABAP_CDS_SELECT_ASSO • OF ONE TO MANY ABAP_CDS_SELECT_ASSO • OF ONE TO ONE ABAP_CDS_SELECT_ASSO
ABAP_CDS_DDL - CDS View Entity, cardinality
ABAP_SYNTAX_FORMS
Cardinality Syntax Written in Words OF ${${EXACT ONE$} $| MANY $| ONE$} TO ${${EXACT ONE$} $| MANY $| ONE$}
Numeric Syntax [min..max]
ABAP_ALTERNATIVES: 1 OF ${EXACT ONE $| MANY $| ONE$} TO ${EXACT ONE $| MANY $| ONE$} 2 ... [min..max] ...
What does it do? Specifies a cardinality to express the relation between the rows of the data sources. The following options are available:
The cardinality can be written in words. In this case, a source and a target cardinality can be specified. This syntax variant is the recommended option.
The cardinality can be specified in numbers in square brackets. In this case, the minimum and maximum cardinality of the association target is specified. No source cardinality can be specified. The implicit default source cardinality is MANY.
The cardinality is optional and can be left out. In this case, the implicit default is many-to-one. SAP recommends to always specify the cardinality explicitly. A cardinality is specified to document the semantics of the data model. Moreover, the SAP HANA database uses the cardinality for performance optimizations. Left outer joins ( LEFT OUTER JOIN) produced by path expressions are given the addition TO ONE if an explicit or implicit to-one cardinality is used and the addition TO MANY if any other cardinality is used. These additions work in the same way as when they are specified explicitly in LEFT OUTER JOIN . This means that an optimization is attempted and the result can be undefined if the result set does not match the cardinality. If no cardinality is specified, the implicit default cardinality is MANY TO ONE.
Latest notes:
To avoid undefined behavior, the cardinality should always be defined to match the data in question.
The specified cardinality is evaluated by the syntax check in the CDS DDL of CDS or in AB_SQL . A non-matching cardinality usually produces a syntax check warning.
The numeric syntax and the syntax written in words must not be mixed. It is not possible, for example, to specify the source cardinality of an association in words and the target cardinality of the same association in numbers. NON_V5_HINTS ABAP_HINT_END
ABAP_EXAMPLE_VX5 The following example shows how an incorrect cardinality leads to unexpected results. A CDS view entity joins the DDIC database tables SCARR and SPFLI in a CDS association _spfli without specifying the cardinality explicitly. The implicitly set cardinality is MANY TO ONE. If the CDS association is used in a path specified in the SELECT list, this is implemented on the SAP HANA database as a left outer join using the addition MANY TO ONE . The actual cardinality of the data is, however, TO MANY. DDLS DEMO_CDS_WRONG_CARDINALITY_1 The class CL_DEMO_CDS_WRONG_CRDNLTY_1 uses different SELECT statements to access the view. The two reads return a different number of rows. This shows that the cardinality should always be defined explicitly to match the data in question to avoid unexpected behavior. ABAP_EXAMPLE_END
ABAP_EXAMPLE_VX5 The following CDS view entity joins the DDIC database tables SCARR and SPFLI in a CDS association _spfli while specifying the cardinality explicitly and correctly. If the CDS association is used in a path specified in the SELECT list, this is implemented on the SAP HANA database as a left outer many-to-many join. This matches the actual cardinality of the data. DDLS DEMO_CDS_EXPLICIT_CARD_1 The class CL_DEMO_CDS_EXPLICIT_CARD_1 uses different SELECT statements to access the view. The two reads return the same number of rows. ABAP_EXAMPLE_END
ABAP Alternative 1 OF ${EXACT ONE $| MANY $| ONE$} TO ${EXACT ONE $| MANY $| ONE$}
What does it do? The cardinality written in words specifies the source and target cardinality, that means, how many entries of the data source match the entries of the association target. The following cardinality specifications are possible:
OF EXACT ONE TO EXACT ONE
OF EXACT ONE TO MANY
OF EXACT ONE TO ONE
OF MANY TO EXACT ONE
OF MANY TO MANY
OF MANY TO ONE
OF ONE TO EXACT ONE
OF ONE TO MANY
OF ONE TO ONE
Latest notes:
SAP recommends using the cardinality syntax written in words instead of the numeric syntax, since it improves query performance in some scenarios.
Further details can be found on SAP Help Portal in the SAP HANA SQL Reference Guide for SAP HANA Platform.
In case of CDS compositions, the source cardinality must be set to EXACT ONE . It can also be left out and is then implicitly set to EXACT ONE . No other source cardinality is valid for CDS compositions.
The cardinality syntax written in words is available in CDS view entities, CDS projection views, CDS custom entities, and CDS abstract entities. It is not available in any other CDS entity. NON_V5_HINTS ABAP_HINT_END
ABAP_EXAMPLE_VX5 The following CDS view entity defines an association with the cardinality EXACT ONE TO ONE to a text provider view. DDLS DEMO_CDS_CARDINALITY_IN_WORDS The SQL statement generated on the database shows that the path expression is transformed into a left outer exact one to one join on the database. IMAGE exact_one.jpg 711 224 ABAP_EXAMPLE_END
ABAP Alternative 2 ... [min..max] ...
What does it do? The numeric syntax specifies the cardinality of the association target of a CDS view entity. The square brackets [ ] are part of the syntax. For min and max , positive integers (including 0) and asterisks (*) can be specified:
max cannot be 0.
An asterisk * for max means any number of rows.
min can be omitted (set to 0 if omitted).
min cannot be *.
When a CDS association is used in a WHERE condition, 1 must be specified for max. If the cardinality is not defined explicitly, the cardinality to-one is used implicitly ([min..1]). With the numeric syntax, no source cardinality can be specified, only the cardinality of the association target. The target cardinality can be specified with high precision, since the exact number of data records returned by an association can be specified. In the SQL statement that is generated on the database, the numeric syntax is translated into words.
Latest notes: SAP recommends using the cardinality syntax written in words instead of the numeric syntax, since it improves query performance in some scenarios. NON_V5_HINTS ABAP_HINT_END
ABAP_EXAMPLE_VX5 The following CDS view entity specifies the cardinality of the association target as to-many using the syntax [*]. DDLS DEMO_CDS_CAR_NUMERIC The SQL statement generated on the database shows that the path expression is transformed into a left outer join. The SQL statement does not contain an explicit cardinality, which means that the implicit default cardinality MANY TO MANY is used. IMAGE num_car_jpg 433 187 ABAP_EXAMPLE_END
