ABAP Keyword Documentation → ABAP Dictionary → ABAP CDS in ABAP Dictionary → ABAP CDS - Views → ABAP CDS - DDL Statements → ABAP CDS - DEFINE VIEW → ABAP CDS - SELECT
ABAP CDS - SELECT, association
Other versions:
7.31 | 7.40 | 7.54
Syntax
... ASSOCIATION [ [min..max] ] TO entity [AS assoc] ON cond_exp ...
Extras
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 joins
the first elementary data source entity specified as the initial data
source (after FROM
using the ON condition
cond_exp) with the data source entity
specified as the target data source (in the definition of the association). A data source entity can be a database table defined in ABAP Dictionary or a view. In the latter case, the view can be an
external view or another CDS view.
An association of a SELECT statement can be accessed - in the same statement at all places where this is documented - by specifying the association name in path expressions. When a CDS view is activated with path expressions, the specified associations are converted to join expressions. The initial data source is shown on the left side and the target data source is shown on 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.
- In all other locations, it is a 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:
- Fields of the initial data source, which are specified in the ON condition, must also be listed in the SELECT list of the current SELECT statement. 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 should be prefixed by $projection and not by the name of initial data source.
- 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
- 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.
- If an association is listed as a special case of a path expression in the SELECT list of the current SELECT statement, other CDS views can be used in the path expressions - provided that the current CDS view is used as the data source. Associations not listed in the SELECT list can only be used in path expressions of the current SELECT statement.
- 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.
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 whole numbers (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 *.
- If min and max have the same values, both sides have the same number of lines.
If the cardinality is not explicitly defined, the cardinality "to 1" is implicitly used ([min..1]).
Cardinality is not validated at runtime. When an association is used in a WHERE condition, 1 must be specified for max.
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.
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. The initial data source in each case is the first database table snwd_so_inv_head after FROM.
- buyer stands for a join between snwd_so_inv_head and the target data source snwd_bpa.
- invoice_items stands for a join between snwd_so_inv_head and the target data source snwd_so_inv_item.
The initial 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..*].
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).
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.
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 }