Get Example source ABAP code based on a different SAP table
• JOIN ABAP_CDS_SELECT_V1 • INNER JOIN ABAP_CDS_SELECT_V1 • LEFT OUTER JOIN ABAP_CDS_SELECT_V1 • RIGHT OUTER JOIN ABAP_CDS_SELECT_V1 • TO ONE ABAP_CDS_SELECT_JOINV1 • TO MANY ABAP_CDS_SELECT_JOINV1 • CROSS JOIN ABAP_CDS_SELECT_V1 • ON ABAP_CDS_SELECT_JOINV1
ABAP Addition 1 ... ON cds_cond 2 ... TO ONE$|MANY
What does it do? Defines a join between two data sources of a ABAP_CDS_V1_VIEW . The code above is part of the syntax of a data source data_source and contains the recursive syntax of a data source data_source. Two joined data sources represent a join expression. Every join expression for an inner or outer join must contain a join condition cds_cond after ON (for details, see Addition 1 below). A join expression for a cross join cannot contain any join condition. Inner joins, outer joins, and cross joins are possible:
A join between two data sources using INNER JOIN or just JOIN selects all entries of the data sources whose fields meet the ON condition.
A join between two data sources using LEFT OUTER JOIN selects all entries on the left side. A join between two data sources using RIGHT OUTER JOIN selects all entries on the right side. Entries that meet the ON condition have the same content as in the inner join. In entries that do not meet the ON condition, the elements on the right or left side have the null value . When the CDS view is used in AB_SQL , null values are set to the type-dependent initial value.
When two data sources are joined using CROSS JOIN, their cross product is produced. All entries on the left side are combined with all entries on the right side. The number of rows in the result set is the number of rows on the left side multiplied by the number of rows on the right side. Nested join expressions are evaluated in the following order:
In the case of inner and outer joins, by the arrangement of the ON conditions. From left to right, the most adjacent ON conditions are assigned to each JOIN and this expression is parenthesized implicitly. These implicit parentheses can be made explicit using actual parentheses, ( ). This is optional.
By default, cross joins are evaluated from left to right. The priority of the evaluation can be affected by parentheses ( ).
If multiple cross joins are combined, the order of the evaluation is irrelevant. The result is always the same and the number of rows is the product of the number of rows of all involved data sources.
If cross joins are combined with inner and outer joins, the result can depend on the order of evaluation or the parentheses.
Latest notes:
A WHERE condition for a SELECT statement with joins affects the result set created using the joins.
An inner join or a cross join between two individual data sources is commutative. If the left and right side are switched, the result remains the same.
Buffering is not recommended for a CDS view that contains an outer join. The result set can contain null values, which means that AB_SQL reads can behave differently to direct database reads when the buffer is read, since null values in the buffer are transformed to initial values.
The function coalesce can be used to prevent null values in the result set.
A cross join behaves like an inner or outer join whose ON condition is always true. A cross join with a WHERE condition has the same result as an inner join with an identical ON condition. Unlike the inner join, in a cross join all data is read first before the condition is evaluated. In an inner join only data that meets the ON condition is read.
A cross join should only be used with extreme caution. Since it is not possible to specify an ON condition, all data of all involved data sources is read. In the case of very large datasets, the result set (whose number of rows is always the product of the number of all rows of both data sources) can quickly become very large.
On the database, a cross join of two client-dependent data sources is defined internally as an inner join, whose ON condition checks whether the client columns of the left and right side are equal. If one side is not client-dependent, the cross join is defined as specified.
In nested join expressions, parentheses are recommended for making the code easier to read. In the case of inner and outer joins, the parentheses can be specified wherever the ON conditions specify parentheses implicitly. NON_V5_HINTS
There is no limit on the number of join expressions in a SELECT statement of a CDS view in the DDL, but there is an ATC check that produces a message once a specific number of expressions is reached. ABAP_HINT_END
ABAP_EXAMPLE_VX5 The following CDS view works in exactly the same way as the DDIC database view DEMO_SCARR_SPFLI. The class CL_DEMO_CDS_JOIN uses SELECT to access the view. Unlike when the DDIC view DEMO_CDS_SCARR_SPFLI is accessed, no client column is returned when the CDS entity DEMO_SCARR_SPFLI is accessed. The CDS-managed DDIC view DEMO_CDS_JOIN returns the client column too. DDLS DEMO_CDS_SCARR_SPFLI ABAP_EXAMPLE_END
ABAP_EXAMPLE_VX5 The following non-parenthesized chaining of join expressions ... from tab1 join tab2 join tab3 on tab2.id = tab3.id on tab1.id = tab2.id ... is parenthesized implicitly as follows: ... from tab1 join ( tab2 join tab3 on tab2.id = tab3.id ) on tab1.id = tab2.id ... No elements from tab1 can be specified in the inner ON condition. ABAP_EXAMPLE_END
ABAP_EXAMPLE_VX5 The following view contains a cross join of table T000 of all clients of an AS ABAP with the entries for the message class SABAPDEMOS in the table T100. The class CL_DEMO_CDS_CROSS_JOIN accesses the view. The result set would be very large without this WHERE condition. DDLS DEMO_CDS_CROSS_JOIN ABAP_EXAMPLE_END
ABAP Addition
What does it do? Join condition. A join condition must be specified for an inner or outer join. A join condition must not be specified for a cross join. The syntax of the relational expressions of a join condition cds_cond is subject to the following restrictions:
General Rules
Character literals cannot be used in comparisons with numeric values.
Numeric literals that represent a value outside the value range of INT8 must be specified as floating point literals with a decimal point.
Special Rules:
All relational operators are allowed. That means all comparison operators are allowed as well as BETWEEN, LIKE, IS [NOT] NULL, and IS [NOT] INITIAL.
lhs expects a field of one of the two data_sources of the join.
rhs expects a field of one of the two data_sources of the join, a literal with optional domain prefix, a parameter, a session variable, or a built-in function.
Path expressions cannot be used.
CDS DDL expressions cannot be used.
ABAP Addition
What does it do? Specifies the cardinality of a left outer join. This addition is positioned after LEFT OUTER , but is not possible after RIGHT OUTER. Only certain specific database systems apply this addition. If the addition TO ONE is specified, any databases that support this addition assume that the result set defined by the left outer join matches this cardinality and the SQL Optimizer attempts to suppress any surplus joins. If the result set does not match the cardinality, the result is undefined and may be dependent on the entries in the SELECT list. If the addition TO MANY is specified, no optimization takes place as a rule.
