ABAP CDS - SELECT, association - ABAP Keyword Documentation

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

ABAP CDS - SELECT, association

Syntax

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

Additions

1. ... [min..max]

2. ... AS _assoc

Effect

Defines an association of the name _assoc in a SELECT statement of a CDS view in ABAP CDS. An association associates the current CDS view as a source data source with the target data source target specified in the definition of the 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.

An 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.

If an association is published using a path expression in the SELECT list of the current SELECT statement,

other CDS views

or Open SQL statements

can use it in their path expressions.

When a CDS view is activated with path expressions, every association specified here is transformed to a join expression. 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 association in a path expression using an attribute.

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

If the 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 association (when used in a path expression). In the ON condition, the field name can be prefixed by $projection instead of the name of the source data source. An alternative element name defined using AS can be used here instead of the field name.

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

Notes

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 associations is a high-value wrapping of the syntax for joins. Using associations instead of directly programming joins makes it easier to read the definition of a CDS view. Associations can be used to model relationships between CDS entities that can be accessed simply using path expressions in CDS views or in Open SQL.

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

Associations and join expressions can be used in a SELECT statement of a CDS view.

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

Addition 1

... [min..max]

Effect

Defines the cardinality of the target data source of a CDS view, which is defined with an 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 an association is used in a WHERE condition, 1 must be specified for max.

If the cardinality is not explicitly defined, the cardinality "to 1" is implicitly used ([min..1]).

When specified, the cardinality is used mainly to document the semantics of the data model. The cardinality is not validated at runtime but can produce syntax check warnings.

Note

The specified cardinality is evaluated by the syntax check for paths specified in the CDS DDL or in Open SQL. Currently, a warning is produced if a value greater than 1 is specified for max, indicating that a path specified in this way influences the cardinality of the results set.

Addition 2

... AS _assoc

Effect

Defines the name _assoc of an association defined using ASSOCIATION of a CDS view. If no name is explicitly defined with 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

We recommend the naming convention of using an underscore _ as the first character of the association name.

Example

Example of a simple 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 association spfli_scarr is published to be used from outside in the SELECT list by specifying a path that contains only the name of an association. The program DEMO_CDS_ASSOCIATION also shows how the association can be accessed by specifying a path in Open 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.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 following databases: snwd_so_inv_head, snwd_so, snwd_bpa, snwd_so_inv_item.

Two 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 recommended prefix $projection is used instead of the prefixes snwd_so_inv_head or snwd_so_inv_head.

The 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]. 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 expression 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 SELECT statement (Open SQL).

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 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 data source in the definition of the CDS view sales_order_invoice_items. This data source is used to access the published association _invoice_items. The elements of the 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 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 }

Continue
ABAP CDS - path_expr