Kontakt

Ansicht
Dokumentation

ABENCDS_JOINED_DATA_SOURCE_V2 - CDS JOINED DATA SOURCE V2

ABENCDS_JOINED_DATA_SOURCE_V2 - CDS JOINED DATA SOURCE V2

BAL_S_LOG - Application Log: Log header data   General Data in Customer Master  
This documentation is copyright by SAP AG.
SAP E-Book

- CDS View Entity, SELECT, JOIN

... ${ $[INNER$] JOIN $}$|${ LEFT$|RIGHT OUTER $[TO ONE$|MANY$] JOIN $}$|${ CROSS JOIN $}
      data_source $[ON cds_cond$] ...

Additions

1. ... ON cds_cond

2. ... TO ONE$|MANY

Effect

Defines a join between two data sources of a CDS view entity. The code above is part of the syntax of a data source data_source and recursively contains the syntax of a second 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.

When joining two or more data sources, each element of the SELECT list must have the name of its data source as prefix.
Example: source1.field

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 match 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 match the ON condition have the same content as in the inner join. In entries that do not match the ON condition, the elements on the right or left side have the null value. When the CDS view entity is used in , null values are set to the type-dependent initial value.
  • When two data sources are joined using CROSS JOIN, the result is their cross product. 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.

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.
  • 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.
  • Cross joins should be used with 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 client-independent, 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 exactly where the ON conditions specify parentheses implicitly.
  • There is no limit on the number of join expressions in a SELECT statement of a CDS view entity in the DDL, but there is an ATC check that produces a message once a specific number of expressions is reached.

Example

The following CDS view entity DEMO_CDS_CLIENT_HANDLING defines different kinds of joins (left outer join, inner join, and right outer join) between the database table T000 and the database table DEMO_SALES_ORDER.

Example

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.

Example

The following view entity 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 program DEMO_CDS_CROSS_JOIN_VE accesses the view entity. Without the WHERE condition, the result set would be very large.

Addition 1

... ON cds_cond

Effect

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:

  • All relational operators are allowed. That means all comparison operators are allowed as well as BETWEEN, LIKE, IS [NOT] NULL, and IS [NOT] INITIAL.
  • The Boolean operators NOT, AND, and OR are allowed.
  • lhs expects a field of one of the two data_sources of the join. If a comparison operator is used as operator, then lhs can also be a literal.
  • Path expressions are not allowed
  • Other CDS DDL expressions are not allowed.

Addition 2

... TO ONE$|MANY

Effect

Specifies the cardinality of a left outer join. This addition is positioned after LEFT OUTER, but it is not possible after RIGHT OUTER. Not all 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 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.

Notes

  • More information can be found in the documentation of the current database system. The SAP HANA database, for example, supports the additions TO ONE and TO MANY and their description is part of the HANA-specific SQL documentation.
  • To avoid undefined and platform-dependent behavior, TO ONE or TO MANY should be specified only if the data to be joined is suitable.

Example

Incorrect use of TO ONE in CDS view entities. The data in the DDIC database tables SCARR and SPFLI do not have the cardinality TO ONE, but TO MANY. On a SAP HANA database, for example, the result is dependent on the SELECT list. If the SELECT list contains columns from both the left and right side of the join expression, no optimization takes place. If the SELECT list doesn't contain any columns from the right side of the join expression, an optimization takes place. If the aggregate function COUNT(*) is used, an optimization takes place. When an optimization takes place, only that data is read that meets the specified cardinality.

The program DEMO_CDS_WRONG_TO_ONE_VE accesses the CDS view entities and represents the results.






Vendor Master (General Section)   BAL Application Log Documentation  
This documentation is copyright by SAP AG.

Length: 18144 Date: 20240418 Time: 042506     sap01-206 ( 202 ms )