SAP NetWeaver AS ABAP Release 751, ©Copyright 2017 SAP AG. All rights reserved.  

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, clauses → 

ABAP CDS - SELECT, UNION

Syntax

... UNION [ALL] select_statement ...

Effect

Creates the union of rows in the results sets of two SELECT statements of a CDS view. A prerequisite is that the structures of the results sets are compatible. This means that the results sets must have the same number of elements and that the pairs of elements in each position have a compatible data type. If no name list is specified, the element names of the result sets must match.

A union results set can itself be the left side of a further union. The properties of the union results set are defined as follows:

• The element names are taken either from an explicitly specified name list or, if no list is specified, the matching names from the SELECT lists of all SELECT statements are used.

• The definition of the key elements is taken from the SELECT list of the first SELECT statement.

• The data type of each element is a data type from ABAP Dictionary whose value range covers the data types of the associated columns of all results sets involved.

• If the addition ALL is not specified, all duplicate entries are removed from the results set. They are not removed if ALL is specified.

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

• They have the same name.

• They have the same cardinality.

• They must be published 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 source and target data source and express the same logic for these fields.

• The fields of the source data source that are listed in conditions must be at identical respective positions in the SELECT list.

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

Notes

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

• A CDS view with union sets cannot be expanded using EXTEND VIEW.

Examples

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

@AbapCatalog.sqlViewName: '...'
define view ... 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 }

Union set with name list. The element names of the SELECT lists do not need to match.

@AbapCatalog.sqlViewName: '...'
define view ... ( c1, c2, c3, c4 ) as
  select
    from demo_join1
      { a, b, c, d }
    union
      select
        from demo_join2
          { d, e, f, g }