SAP UNION ABAP Statements
Get Example source ABAP code based on a different SAP table
• UNION ABAP_STATEMENT_OSQL
• INTERSECT ABAP_STATEMENT_OSQL
• EXCEPT ABAP_STATEMENT_OSQL
UNION, INTERSECT, EXCEPT ABAP_SYNTAX_FORMS UNION, INTERSECT, and EXCEPT in Main Queries 1
$[WITH ...$]
SELECT query_clauses
${UNION $[ALL$|DISTINCT$]$}
$| ${INTERSECT $[DISTINCT$]$}
$| ${EXCEPT $[DISTINCT$]$}
$[($] SELECT query_clauses $[)$]
$[${UNION $[ALL$|DISTINCT$]$}
$| ${INTERSECT $[DISTINCT$]$}
$| ${EXCEPT $[DISTINCT$]$}
...$]
$[ORDER BY sort_key$]
INTO$|APPENDING target
$[abap_options$].
...
$[ENDSELECT$|ENDWITH.$]
2 OPEN CURSOR ... FOR
$[WITH ...$]
SELECT query_clauses
${UNION $[ALL$|DISTINCT$]$}
$| ${INTERSECT $[DISTINCT$]$}
$| ${EXCEPT $[DISTINCT$]$}
$[($] SELECT query_clauses $[)$]
$[${UNION $[ALL$|DISTINCT$]$}
$| ${INTERSECT $[DISTINCT$]$}
$| ${EXCEPT $[DISTINCT$]$}
...$]
$[ORDER BY sort_key$]
$[abap_options$].
UNION, INTERSECT, and EXCEPT in Subqueries
3 ... ( SELECT query_clauses
${UNION$[ALL$|DISTINCT$]$}
$| ${INTERSECT $[DISTINCT$]$}
$| ${EXCEPT $[DISTINCT$]$}
$[($] SELECT query_clauses $[)$]
$[${UNION $[ALL$|DISTINCT$]$}
$| ${INTERSECT $[DISTINCT$]$}
$| ${EXCEPT $[DISTINCT$]$}
...$] ) ...
ABAP_VARIANTS:
1 ... UNION ...
2 ... INTERSECT ...
3 ... EXCEPT ...
ABAP Addition
... ALL$|DISTINCT
What does it do?
The set operators UNION, INTERSECT, and EXCEPT merge the result sets of multiple queries into a single result set. The syntax forms show where UNION , INTERSECT, and EXCEPT can be used: For creating the result set of the main query of a standalone statement, which is introduced using WITH or SELECT. For creating the result set of the main query after OPEN CURSOR. For creating the result set of a parenthesized subquery in a relational expression, the definition of a common table expression after WITH, or an INSERT statement.
In all syntax forms, it is possible to specify the same clauses and additions, specifically query_clauses, for SELECT statements of queries in front of and after UNION , INTERSECT, or EXCEPT for defining result sets. A query on the right side of UNION, INTERSECT, or EXCEPT can be enclosed in parentheses ( ). A pair of parentheses can include multiple unions, intersections, or differences. The queries joined with UNION, INTERSECT, or EXCEPT are evaluated from left to right. Specific statements can be prioritized using parentheses.
In the case of a standalone SELECT or WITH statement and after OPEN CURSOR, the ORDER BY and the abap_options after the last query or after the position of the last closing bracket are listed and affect the merged result set. In the case of standalone statements, the INTO clause is to be listed as the last clause and in front of the additions abap_options. The following special features apply to standalone statements with set operators:
ORDER BY clause
The addition ORDER BY PRIMARY KEY is not allowed.
Columns of the merged result set specified after ORDER BY must occur with the same name in all relevant SELECT statements. The names must be specified directly and cannot be specified with the column selector ~ after a column name.
INTO clause
If the addition CORRESPONDING or an inline declaration @DATA$|@FINAL(...) is used in the INTO clause, the column names of all result sets defined in the query_clauses from left to right must match.
The merged result set is always multirow. When assigning to a non-table target area, that is, a SELECT statement without the addition INTO$|APPENDING ... TABLE, a loop to be terminated with ENDSELECT or ENDWITH is always opened.
Restricting the result set
The additions UP TO and OFFSET are not currently allowed with UNION, INTERSECT, and EXCEPT.
Since the result set is tabular, SINGLE cannot be used.
Latest notes:
Prioritizations using parentheses are particularly applicable when handling duplicate rows using DISTINCT.
When UNION, INTERSECT, or EXCEPT is used, AB_SQL bypasses table buffering.
The maximum number of different SELECT statements that can be joined using UNION, INTERSECT, or EXCEPT depends on the database system. If this number is exceeded, an exception is raised when the program is executed.
NON_V5_HINTS
ABAP_HINT_END
ABAP_VARIANT_1 ... UNION ...
What does it do?
The AB-SQL set operator UNION merges the result sets of two queries. The rows of the result set of the query after UNION are inserted into the result set of the query in front of UNION.
Latest notes:
NON_V5_HINTS
When UNION is used, the syntax check is performed in a ABAP_STRICT_760 strict mode / , which handles the statement more strictly than the regular syntax check. More specifically, the INTO clause and the additions abap_options must be specified at the end of the entire SELECT statement.
ABAP_HINT_END
ABAP_EXAMPLE_VX5
Creates the union of one row from the DDIC database table SCARR with multiple rows from the DDIC database table SPFLI. Those columns that do not exist in the other table are replaced by literals. Here, a CAST is required for the column CONNID .
ABEXA 00749
ABAP_EXAMPLE_END
ABAP_EXAMPLES_ABEXA
UNION - Examples
ABAP_EXAMPLE_END
ABAP_VARIANT_2 ... INTERSECT ...
What does it do?
The AB-SQL set operator INTERSECT returns all distinct rows of the result set of the query in front of INTERSECT that are also present in the result set of the query after INTERSECT.
Latest notes:
NON_V5_HINTS
When INTERSECT is used, the syntax check is performed in a ABAP_STRICT_783 strict mode / , which handles the statement more strictly than the regular syntax check. More specifically, the INTO clause and the additions abap_options must be specified at the end of the entire SELECT statement.
ABAP_HINT_END
ABAP_EXAMPLE_VX5
Creates the intersect of multiple rows from a joined table with multiple rows from the DDIC database table SCARR.
ABEXA 01672
ABAP_EXAMPLE_END
ABAP_EXAMPLES_ABEXA
INTERSECT - Examples
ABAP_EXAMPLE_END
ABAP_VARIANT_3 ... EXCEPT ...
What does it do?
The AB-SQL set operator EXCEPT returns all distinct rows of the result set of the query in front of EXCEPT that are not present in the result set of the query after EXCEPT.
Latest notes:
AB-SQL does not support the alternative syntax MINUS that is available for the SAP HANA database.
NON_V5_HINTS
When EXCEPT is used, the syntax check is performed in a ABAP_STRICT_783 strict mode / , which handles the statement more strictly than the regular syntax check. More specifically, the INTO clause and the additions abap_options must be specified at the end of the entire SELECT statement.
ABAP_HINT_END
ABAP_EXAMPLE_VX5
Selects all airline codes and airline names from the DDIC database table SCARR that do not exist in the DDIC database table SPFLI.
ABEXA 01673
ABAP_EXAMPLE_END
ABAP_EXAMPLES_ABEXA
EXCEPT - Examples
ABAP_EXAMPLE_END
• ALL UNION
• DISTINCT UNION
• DISTINCT INTERSECT
• DISTINCT EXCEPT
ABAP Addition
What does it do?
The additions ALL and DISTINCT control how duplicate rows are handled. DISTINCT is the default here:
If the addition ALL is specified, all rows from the result set of the right SELECT statement are inserted into the existing result set.
If the addition DISTINCT is specified or if neither of the two additions is specified, the behavior is as follows:
First, all rows of the result set of the right SELECT statement are inserted into the existing result set.
Then all rows that occur more than once are deleted except for one. For determining the duplicate rows, all columns of the result set are respected.
Latest notes:
The default behavior or the addition DISTINCT is always applied to the entire existing result set of the left side. The addition DISTINCT also removes any duplicate rows produced by the addition ALL of preceding UNION additions.
The AB-SQL set operator UNION can be used together with the addition ALL or DISTINCT.
The AB-SQL set operators INTERSECT and EXCEPT can be used together with the addition DISTINCT.
NON_V5_HINTS
ABAP_HINT_END
ABAP_EXAMPLE_VX5
The DDIC database table DEMO_EXPRESSIONS is filled with one row and the union of the table with itself is created. Using the addition DISTINCT, the result set contains one row and using the addition ALL it contains two rows.
ABEXA 00750
ABAP_EXAMPLE_END
Return to menu
