ABAP Keyword Documentation → ABAP - Dictionary → ABAP CDS in ABAP Dictionary → ABAP CDS - Data Definitions → ABAP CDS - DDL for Data Definitions → ABAP CDS - DEFINE VIEW → ABAP CDS - SELECT

ABAP CDS - SELECT, ASSOCIATION

Other versions: ~~7.31~~ | 7.40 | 7.54

Syntax


... ASSOCIATION [ [min..max] ] TO target [AS _assoc] ON
cond_exp 
    
           [ WITH DEFAULT FILTER cond_exp ] ...

Extras

1. ... [min..max]

2. ... AS _assoc

3. ... WITH DEFAULT FILTER cond_exp

Effect

Defines a CDS association with the name _assoc in a SELECT statement of a CDS view. A CDS association associates the current CDS view as a source data source with the target data source target specified in the definition of the CDS association using an ON condition cond_exp. A data source target can be a database table defined in ABAP Dictionary , a classic view, an external view, or a CDS entity. If target is an abstract CDS entity, the way the CDS association can be used is restricted.

A CDS association of a SELECT statement in a CDS view can be accessed as follows:

By specifying its name in a path expression in the same statement and in all places where this is documented. A CDS association whose target data source is an abstract CDS entity can only be published in the SELECT list.

If a CDS association whose target data source is not abstract is published using a path expression in the SELECT list of the current SELECT statement, the following can use it in their path expressions:

Other CDS views

ABAP SQL statements

Published self associations can be specified as hierarchy associations in CDS hierarchies or in the ABAP SQL hierarchy generator HIERARCHY.

When a CDS view is activated that uses a path expression to access a CDS association or an element, or when a path expression is used for access in ABAP SQL, every CDS association of the path expression is transformed to a join expression. Here, the source data source represents the left side and the target data source represents the right side. The ON condition of the association is added to the ON condition of the join. By default, the category of the join is determined by where the path expression is used:

After FROM, it is an inner join (INNER JOIN)

In all other locations, it is a left outer join (LEFT OUTER JOIN)

This setting can be overwritten when specifying the CDS association in a path expression using an attribute. A path expression in a SELECT list that publishes its closing CDS association is not defined as a join. A self association whose target data source is the same as the source data source cannot be created as a join in the CDS view where it is defined.

When specifying the ON condition, the following special rules apply:

The fields of the target data source must be prefixed in the ON condition by the name of the CDS association (prefix _assoc. separated by a period).

If the CDS association in the SELECT list of the current SELECT statement is published, the fields of the source data source specified in the ON condition must also be listed in the SELECT list. This ensures that a join expression can be built from the CDS association (when used in a path expression).

To make a reference to an element of the SELECT list in the ON condition, the field name can be prefixed with $projection instead of the source data source. In this case, an alternative element name defined using AS can be specified instead of the field name. If a path expression of the SELECT list is used when the prefix $projection is used in the ON condition, the current CDS association cannot itself be used in the SELECT list, to avoid invalid join expressions.

Notes

CDS associations not listed in the SELECT list can only be used in path expressions of the current SELECT statement.

The syntax for defining and using CDS associations is a higher-value wrapping of the syntax for joins. Using CDS associations instead of directly programming joins makes it easier to read the definition of a CDS view. CDS associations can be used to model relationships between CDS entities that can be accessed simply using path expressions in CDS views or in ABAP SQL.

When a CDS view is activated, a join defined by a CDS association is built for every use in a path expression and not for the definition of the CDS association. No joins are constructed for CDS associations that are not used in their CDS views.

If a CDS association is used in a path expression of the SELECT list of the current SELECT statement, a join is defined for the current view. Here, the ON conditions of the CDS association are applied to the join directly. No path expressions are allowed in the ON condition of a join, which means that no reference can be made to a path expression of the SELECT list when using the prefix $projection in the ON condition of a CDS association, if CDS the association is also used here.

CDS associations and join expressions can be used in a SELECT statement of a CDS view. When the join expressions defined by the CDS associations are used in path expressions, all joins are joined.

Special rules apply to CDS associations in SELECT statements joined with UNION.

Cyclical dependencies should be avoided when using CDS associations to prevent problems occurring in mass activations of CDS entities.

CDS associations whose target data source is an abstract CDS entity cannot be used anywhere where they could produce instances of join expressions.

Addition 1

... [min..max]

Effect

Defines the cardinality of the target data source of a CDS view, which is defined with a CDS association ASSOCIATION. 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 1" is used implicitly ([min..1]).

A cardinality is specified to document the semantics of the data model and, in some database systems, for optimizations. In these database systems, LEFT OUTER JOINs produced by a path expressions are given the addition TO ONE if an explicit or implicit "to 1" 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 results set does not match the cardinality.

Notes

To avoid undefined and platform-dependent behavior, the cardinality should always be defined to match the data in question.

The specified cardinality is evaluated by the syntax check for paths specified in the CDS DDL of CDS or in ABAP SQL. A non-matching cardinality usually produces a syntax check warning.

Example

Specifies the cardinality of a CDS association incorrectly. The following CDS view joins the database tables SCARR and SPFLI in af CDS association _spfli without specifying the cardinality explicitly. The implicitly set cardinality is "to 1". If the CDS association is used in a path specified in the SELECT list, this is characterized in some database systems (for example the SAP HANA database) as a left outer join using the implicit addition TO ONE. The actual cardinality of the data is, however, TO MANY.

@AbapCatalog.sqlViewName: 'DEMOCDSWRGC'
define view demo_cds_wrong_cardinality
  as select from
    scarr
    association to spfli as _spfli on
      _spfli.carrid = scarr.carrid
    {
      scarr.carrid   as carrid,
      scarr.carrname as carrname,
      _spfli.connid  as connid
    }

The program DEMO_CDS_WRONG_CARDINALITY uses different SELECT statements to access the view. On optimizing database systems, such as the SAP HANA database, the two reads return a different number of rows, potentially an unexpected result.

Example

Specifies the cardinality of a CDS association correctly. The following CDS view joins the database tables SCARR and SPFLI in a CDS association _spfli while specifying the cardinality explicitly. If the CDS association is used in a path specified in the SELECT list, this is characterized in some database systems (for example the SAP HANA database) as a left outer join using the implicit addition TO MANY, which matches the actual cardinality of the data.@@DDL@@DEMO_CDS_EXPLICIT_CARDINALITY@@

The program DEMO_CDS_EXPLICIT_CARDINALITY uses different SELECT statements to access the view. The two reads return the same number of rows on all database systems.

Addition 2

... AS _assoc

Effect

Defines the name _assoc of a CDS association of a CDS view defined using ASSOCIATION. If no name is defined explicitly using AS, _assoc is set implicitly to the name of the target data source. The name _assoc must comply with the naming rules for names.

Note

It is advisable to use an underscore _ as the first character of the CTE CDS association name.

Example

Example of a simple CDS association. The following CDS view provides the same result as the CDS view DEMO_CDS_SCARR_SPFLI in the joins example, as shown in the program DEMO_CDS_ASSOCIATION using an assertion. Furthermore, the CDS association spfli_scarr is published to be used from outside in the SELECT list by specifying a path that contains only the name of a CDS association. The program DEMO_CDS_ASSOCIATION also shows how the CDS association can be accessed by specifying a path in ABAP SQL.

@AbapCatalog.sqlViewName: 'DEMO_CDS_ASSOC'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_association(
_spfli_scarr,
id,
carrier,
flight,
departure,
destination
)
as select from
spfli
association [1..1] to scarr as _spfli_scarr on
$projection.carrid = _spfli_scarr.carrid
{
_spfli_scarr,
key spfli.carrid,
key _spfli_scarr[inner].carrname,
key spfli.connid,
spfli.cityfrom,
spfli.cityto
}

Example

The following CDS view sales_order_invoice_header returns information about sales invoices and works with the database tables snwd_so_inv_head, snwd_so, snwd_bpa, and snwd_so_inv_item.

Two CDS associations are defined:

_buyer stands for a join between the current view and the target data source snwd_bpa.

_invoice_items stands for a join between the current view and the target data source snwd_so_inv_item.

The source data source fields used in the ON conditions - node_key and buyer_guid - are part of the SELECT list. Here the prefix $projection is used instead of the prefixes snwd_so_inv_head or snwd_so_inv_head.

The CDS association _buyer is not listed in the SELECT list and can only be used in path expressions of the current SELECT statement. This association can be specified in the WHERE condition due to the cardinality [1..1]. The CDS association _invoice_items is not accessed in path expressions of the current SELECT statement. However, this association is listed in the SELECT list, which means it can be used in path expressions of other CDS views. This association cannot be specified in a WHERE condition due to the cardinality [1..*].

@AbapCatalog.sqlViewName: 'SALESO_INVHDR_VW'
define view sales_order_invoice_header as
  select from snwd_so_inv_head
           inner join snwd_so
             on snwd_so_inv_head.so_guid = snwd_so.node_key
         association [1..1] to snwd_bpa as _buyer
           on $projection.buyer_guid = _buyer.node_key
         association [1..*] to snwd_so_inv_item as _invoice_items
           on $projection.node_key = _invoice_items.parent_key
         { key snwd_so_inv_head.node_key,      //used in assoc _invoice_items
               snwd_so_inv_head.buyer_guid,    //used in assoc _buyer
               snwd_so.so_id as sales_order_id,
               _buyer.bp_id as buyer_id,       //from assoc _buyer
               snwd_so_inv_head.payment_status,
              @Semantics.currencyCode
               snwd_so_inv_head.currency_code,
              @Semantics.amount.currencyCode: 'currency_code'
               snwd_so_inv_head.gross_amount,
               _invoice_items                 //publish assoc _invoice_items
         }
         where _buyer.bp_role = '001';          //usage of assoc buyer

The CDS view can be accessed in an ABAP program with a simple ABAP SQL SELECT statement.

SELECT sales_order_id, buyer_id, payment_status
FROM sales_order_invoice_header
INTO CORRESPONDING FIELDS OF TABLE @itab.

The complexity of the actual query is wrapped transparently in the CDS view for the application programmer. When the view is accessed, the join (defined by the CDS association _invoice_items) between snwd_so_inv_head and snwd_so_inv_item is not built, because there are no path expressions that need to access the join.

The CDS view sales_order_invoice_header mentioned above is used as the the data source in the definition of the CDS view sales_order_invoice_items. This data source is used to access the published CDS association _invoice_items. The elements of the CDS association are accessed in this view. There is no visual indication that it is the result of a join. This join between snwd_so_inv_head and snwd_so_inv_item is created when the CDS view sales_order_invoice_items is activated. The other CDS association _buyer of the CDS view sales_order_invoice_header cannot be accessed.

@AbapCatalog.sqlViewName: 'SALESO_INVITM_VW'
define view sales_order_invoice_items as
  select from sales_order_invoice_header as header
  { header.sales_order_id,
    header._invoice_items.inv_item_pos as item_position,
   @Semantics.currencyCode
    header._invoice_items.currency_code,
   @Semantics.amount.currencyCode: 'currency_code'
    header._invoice_items.gross_amount }

Addition 3

... WITH DEFAULT FILTER cond_exp

Effect

Defines a standard filter condition for a path expression.

If no filter condition is specified when the CDS association is used in an path expression in the attributes attributes, the condition cond_exp specified using DEFAULT FILTER is used as the filter condition and applied in an extended condition for the join. The same rules apply to the default filter condition as to a filter condition specified as an attribute.

If a filter condition is specified when the CDS association is used in a path expression in the attributes attributes, this condition is used instead of the default filter condition.

Note

When the syntax check evaluates a cardinality specified using [min..max], the default filter condition is respected alongside the ON condition.

Continue

ABAP CDS - path_expr