Ansicht
Dokumentation

ABAPUNION_CLAUSE - UNION CLAUSE

ABAPUNION_CLAUSE - UNION CLAUSE

General Data in Customer Master   ABAP Short Reference  
This documentation is copyright by SAP AG.
SAP E-Book

UNION, INTERSECT, EXCEPT, query_clauses



Effect

Possible clauses and additions of queries whose result sets are joined using UNION, INTERSECT, or EXCEPT. The result set of SELECT statements joined using UNION, INTERSECT, or EXCEPT is regarded as having multiple rows.

The following special conditions apply to clauses and additions of the SELECT statements joined using UNION, INTERSECT, or EXCEPT:

  • All SELECT lists of SELECT statements grouped using UNION, INTERSECT, or EXCEPT must have the same number of elements.
  • The columns assigned to each other must have the same type attributes with respect to built-in data type, length, and number of digits after the decimal point, with the following exceptions:
  • Numeric types INT1, INT2, INT4 and INT8 can create a column. The resulting column has the data type with the greatest value range.

  • Numeric types DEC can have different lengths but must have the same number of decimal places. The resulting column has the data type with the greatest length. The corresponding special types CURR and QUAN are handled here like DEC.

  • The numeric types DF16_DEC and DF34_DEC are handled like the numbers of type DEC as they are stored, and the rule above applies with respect to lengths and decimal places.

  • Character-like types CHAR can have different lengths. The resulting column has the data type with the greatest length. The corresponding special types CLNT, LANG, CUKY, and UNIT are handled here like CHAR.

All other types must be exactly the same. This applies specifically to NUMC and RAW, where the lengths must match. The different categories of strings cannot be combined either.
  • The addition FOR ALL ENTRIES is not allowed in the WHERE conditions of the SELECT statements involved.
  • An ORDER BY clause cannot be specified for individual result sets. Instead, ORDER BY can be applied to the union, intersection, or exception result set of main queries.

The number of SELECT statements that can be joined using UNION, INTERSECT, or EXCEPT is not limited by a fixed value.

Notes

  • Each of the joined SELECT statements has its own client handling. An addition USING or the obsolete addition CLIENT SPECIFIED in the FROM clause only affects the SELECT statement for which it is specified.
  • If required, the column names of the individual result sets can be modified using alias names after AS.
  • If the column names of the result sets are not identical, the column names are used from the result set of the SELECT statement on the left of UNION, INTERSECT, or EXCEPT. In this type of case, the names are usually not visible, except for subqueries in the WITH statement.
  • The numeric types DF16_DEC and DF34_DEC can only be used together in a column if their decimal places match.
  • SQL expressions, host expressions, and host variables can be used to synchronize the columns of the result set of the SELECT statements involved.

Example

Creates the union set of three aggregate expressions on the same DDIC database table in a subquery of a WITH statement. The union set is selected in a common table expression, which makes it possible to use a single WHERE condition for the entire result set in the main query of the WITH statement. If the union set were created in a main query, every single SINGLE statement would have to contain the same WHERE condition or HAVING condition.






BAL_S_LOG - Application Log: Log header data   Vendor Master (General Section)  
This documentation is copyright by SAP AG.

Length: 8143 Date: 20240329 Time: 002146     sap01-206 ( 118 ms )