Documentation View

We are hiring and constantly growing! Have a look through our vacancies to find the right role for you!

ABAPUNION_CLAUSE - UNION CLAUSE

ABAPUNION_CLAUSE - UNION CLAUSE

Fill RESBD Structure from EBP Component Structure   RFUMSV00 - Advance Return for Tax on Sales/Purchases  
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.






Addresses (Business Address Services)   PERFORM Short Reference  
This documentation is copyright by SAP AG.

Length: 8143 Date: 20230207 Time: 090837     sap01-206 ( 146 ms )