Ansicht
Dokumentation

ABENCDS_UNION_V2 - CDS UNION V2

CPI1466 during Backup General Data in Customer Master
This documentation is copyright by SAP AG.

- CDS View Entity, SELECT, UNION

Effect

Merges the rows of the result sets of multiple SELECT statements of into one result set. As a prerequisite, the result sets must have the same number of elements and the element pairs that occur in the same position of the result set must have a compatible data type. A union result set can itself be the left side of a further union. If the addition ALL is specified, all entries, even duplicates, are listed in the result set. If ALL is not specified, all duplicate entries are removed from the result set.

Data Types

If the addition ALL is specified, all data types are possible in a union view. Elements with data types LRAW and LCHR must be at the end of the view and an element of type INT2 or INT4 must stand directly in front of such an element, representing the maximum length of the element. Only one such element is allowed per view.

Without the addition ALL, data types STRING, RAWSTRING, LCHR, LRAW, and GEOM_EWKB are not supported.

As a prerequisite for using the statement UNION in a CDS view entity, the CDS view entity annotation Metadata.ignorePropagatedAnnotations: true must be specified.

Rules for the Elements of a UNION View

In general, the first SELECT statement defines the signature of the UNION view. Here are the details on the properties of the elements of the merged result set:

Element names

The direct element names or the alternative element names defined using AS must match for each column in the SELECT lists of all SELECT statements and are used.

Key elements

They key elements of all SELECT lists must match. Otherwise, a syntax check error occurs.

Element annotations

Only the SELECT list of the first SELECT statement can define element annotations. In all subsequent branches, element annotations a forbidden.

Data type

The SELECT list of the first SELECT statement determines the DDIC data type of each element of the CDS entity.

The data type used to create the union set on the database is a data type whose value range covers the data types of the associated columns of all result sets involved.

The following table shows which data types can be merged with each other in a union. On the left side, the data types of the SELECT list of the first SELECT statement are listed and the other columns specify with which data types of the following SELECT lists they can be merged.

First/following

INT1

INT2

INT4

INT8

DEC

DF16_...

DF34_...

CURR

QUAN

FLTP

CHAR

SSTRING

STRING

NUMC

DATS

DATN

TIMS

TIMN

UTCL

ACCP

CLNT

LANG

UNIT

CUKY

RAW

GEOM_EWKB

INT1

INT2

INT4

INT8

DEC

DF16_...

DF34_...

CURR

QUAN

FLTP

CHAR

SSTRING

STRING

NUMC

DATS

DATN

TIMS

TIMN

UTCL

ACCP

CLNT

LANG

UNIT

CUKY

RAW

GEOM_EWKB

There are no further restrictions to note in combinations using "x". The following rules apply to the other combinations:

In combinations with "w", the length or the value range of the data type in the first SELECT must be long enough for all following SELECT lists. If this is not the case, a syntax check warning is raised.

In combinations using "l", the lengths of the data types must match exactly.

In combinations using "d", the number of decimal places must match exactly.

If the length or value range of the first element does not cover the following elements in combinations with "w", the value returned by the database can be too great for the data type of the element of the CDS entity. It is still possible to activate the view here, but the content may be truncated or exceptions may be raised in queries if a type reference is applied to the entity. This is due to the assignment rules of the INTO clause. Assignments to ABAP data objects with a sufficiently large value range, on the other hand, do not cause problems.

Amounts and Quantities in UNION Views

If the elements of a UNION view are CDS amount fields, CDS quantity fields, or CDS calculated quantities, the following rules apply:

Since the annotation Metadata.ignorePropagatedAnnotations: true is mandatory in UNION views, all reference annotations are lost and must be provided again within the first SELECT branch.

For elements of data type CURR or QUAN as well as for calculated quantities, the reference annotation is mandatory.

Elements of other data types lose their reference information and their characteristic as amount or quantity field. The respective reference annotation can be assigned anew to declare the field an amount or quantity field. This is optional.

Calculated quantity fields can be merged only with other calculated quantity fields. Amount fields and quantity fields can be merged with each other, as long as the data types match (see matrix above).

For amount fields of type CURR, the number of decimal places must match exactly in all UNION branches.

Elements of data type CURR are incompatible to any other data type. The function CURR_TO_DECFLOAT_AMOUNT can be used to convert an amount field of data type CURR into an amount field of data type DECFLOAT34.

Associations in UNION Views

A CDS view entity in which union sets are formed with UNION can expose CDS associations. Such an association must be defined and exposed in the same way in all SELECT statements merged with UNION. That is, CDS associations that are exposed in the respective SELECT lists must appear in all SELECT statements merged with UNION and the following requirements must be met:

They have the same cardinality.

They must be exposed at the same position in the SELECT list using a path expression.

They must have the same ON conditions, that is:

The conditions must relate to the same fields in the association source and association target and express the same logic for these fields.

The fields of the association source that are listed in an ON condition must be at identical positions in the SELECT list.

It is not required that the associations are defined with the same alias name.

These rules also apply if a CDS association is defined in a data source of a SELECT statement and is exposed by the current statement. From outside, the CDS associations with the same name that are exposed in the individual SELECT lists act like a CDS association exposed by the union set. There are no restrictions on CDS associations that are not exposed in the SELECT lists.

Nesting of UNION Branches

In CDS view entities, nesting of union clauses is possible. To nest a union clause within another union clause, use brackets ( ... ). A nested union clause is merged first and its result set is then merged again with the result sets of the other union branches.

Example number 3 below demonstrates nesting of union branches.

Defining new CDS compositions is not possible in a UNION view. Exposing CDS compositions and to-parent associations that were defined in a data source of the SELECT statements is possible.

Annotations are only allowed in the SELECT list of the first SELECT statement. In all following branches, annotations are not allowed.

Notes

Union sets can be a good way of transforming non-normalized DDIC database tables into a normalized view of the data.

If the length of the value range of an element from the SELECT list of the first SELECT statement does not cover the associated elements of the following SELECT statements, it is advisable to define an appropriate type with a suitable CAST expression.

The maximum number of different SELECT statements that can be merged using UNION depends on the database system. If this number is exceeded, the CDS view entity cannot be activated.

Example

Union set. The element names of the SELECT lists must match.

define view entity ... as
  select
    from demo_join1
      { a as c1, b as c2, c as c3, d as c4 }
    union
      select
        from demo_join2
          { d as c1, e as c2, f as c3, g as c4 }

Example

The following CDS view entity creates the union of the result sets of two SELECT statements. The element col1 in the first SELECT list has the type INT4, whereas the associated element col1 in the second SELECT list has the type INT8. This is why a syntax check warning is raised. To bypass the warning, col2 is converted to data type INT8 using a CAST expression.

The program DEMO_CDS_UNION_ELEMENT_TYPE_VE uses SELECT to access the view four times:

The first access writes directly to an internal table with the line type of the CDS entity. The access works because the value of the element col1 matches the value range of the data type i.

The second access is the same as the first but one value in the element col1 is outside the value range of the data type i of the first column of the internal table result2. This is why an exception is raised.

In the third access, the exception is prevented since the data type of the first column of the result set (and hence the data type of the internal table result3) is transformed to int8 using a CAST in .

In the fourth access, the exception is prevented due to an appropriate declaration of the data type of the first column of the internal table result4.

The recommended method in all cases, however, is to handle the second element using a CAST in the CDS view entity.

Example - Nesting of Union Clauses

The following CDS view entity nests a union clause within another union clause.

The program DEMO_CDS_UNION_NESTING_VE first fills the underlying database table and then accesses the view using SELECT. The result set consists of 9 rows. Two of them are duplicates, marked in yellow:

IMAGE @@nesting_result.png@@170@@213@@

If there were no nesting and the brackets ( ) around the last two union branches were left out, the result set would return only 7 rows. The reason is that the third union branch doesn't use the addition ALL. With nesting, duplicate entries are removed only from the last two branches, which are nested within each other.

Without nesting, duplicate entries would be removed from all union branches and the result set would contain fewer rows.

Addresses (Business Address Services) TXBHW - Original Tax Base Amount in Local Currency
This documentation is copyright by SAP AG.

Length: 45067 Date: 20240419 Time: 175356 sap01-206 ( 345 ms )

Ansicht Dokumentation

ABENCDS_UNION_V2 - CDS UNION V2

- CDS View Entity, SELECT, UNION

Effect

Data Types

Rules for the Elements of a UNION View

Amounts and Quantities in UNION Views

Associations in UNION Views

Nesting of UNION Branches

Notes

Example

Example

Example - Nesting of Union Clauses

Ansicht
Dokumentation