SAP S/4 HANA > CDSVIEWS

UNION AND UNION ALL IN CDS VIEWS

UNION:

Union

A union results set can itself be the left side of a further union.

Prerequisites

Compatible Structure of Result Sets

  • Same number of elements
  • Elements in same position have compatible data type
  • Elements in same position have identical name/alias

Properties of Result SET

Element Names

Are (identical) in both original result sets and are taken from there

Key Definition

taken from first SELECT statement

Element Type

ABAP Dictionary type with sufficient value range

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.
Union Example
  • The CDS view in this example reads flight customers from table SCUSTOM and travel agencies from table STRAVELAG. The two result sets are merged into one by using key word UNION.
  • A field with name TYPE is filled with literal ‘Customer’ or ‘Agency ‘ to make the rows in the result set distinguishable. This field is a key field of the resulting view because it is labelled with “key” in the first select.

Note:The two literals have to be of identical length to fulfill the requirement, that corresponding elements need compatible types!

UNION ALL:

UNION ALL

A prerequisite for UNION (ALL) 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. The element names of the result sets must match.

UNION ALL will display result set from both the tables.

When you compare the performance UNION ALL is much faster than UNION while displaying results.

Please find the below example for difference between UNION AND UNION ALL

difference between union and union all

The CDS views in this example read customer ids from table SCUSTOM (Customers with address in ‘Walldorf’) and from table SBOOK (customers that booked with travel agency ‘00000100’).

In the first data definition key word UNION ALL is used. The second CDS view reads exactly the same data but uses UNION without addition ALL.

Customer ‘00000001’ has an address in Walldorf and made one booking with travel agency ‘00000100’. Consequently, the result set of the first CDS View contains customer ID ‘00000001’ twice. With keyword ‘UNION’ instead of ‘UNION ALL the second entry is removed from the result set.

Please write us here for any queries and concerns.

Related Articles