SAP CDS EXCEPT V2

Get Example source ABAP code based on a different SAP table

• EXCEPT ABAP_CDS_SELECT

ABAP_CDS_DDL - CDS View Entity, SELECT, EXCEPT

ABAP_SYNTAX
... EXCEPT select_statement ...

What does it do?
The set operator EXCEPT returns all rows of a SELECT statement of a ABAP_CDS_V2_VIEW that are not part of the result sets of the following SELECT statements. As a prerequisite, the result sets must have the same number of elements and the element pairs that occur in the same position of the result set must have a compatible data type. All data types except for STRING, RAWSTRING, LCHR, LRAW, and GEOM_EWKB are supported. An EXCEPT result set can itself be the left side of a further EXCEPT.

ABAP_PREREQUISITE
As a prerequisite for using the statement EXCEPT in a CDS view entity, the CDS view entity annotation Metadata.ignorePropagatedAnnotations: true must be specified.
ABAP_PREREQ_END

Rules for the Elements of a View Using EXCEPT
The first SELECT statement defines the signature of the view using EXCEPT. The elements of the result set of the EXCEPT view have the following properties:

Element names The direct element names or the alternative element names defined using AS must match for each column in the SELECT lists of all SELECT statements and are used.

Key elements They key elements of all SELECT lists must match. Otherwise, a syntax check error occurs.

Element annotations Only the SELECT list of the first SELECT statement can define element annotations. In all subsequent branches, element annotations are forbidden.

Data type

The SELECT list of the first SELECT statement determines the DDIC data type of each element of the CDS entity.

The data type used to create the result set on the database is a data type whose value range covers the data types of the associated columns of all result sets involved.
The following table shows which data types can be merged with each other in a view using EXCEPT. On the left side, the data types of the SELECT list of the first SELECT statement are listed and the other columns specify with which data types of the following SELECT lists they can be merged. First/followingINT1INT2 INT4INT8DEC DF16_... DF34_... CURR QUANFLTPCHAR SSTRING STRINGNUMCDATS DATN TIMS TIMNUTCL ACCPCLNTLANG UNITCUKYRAWGEOM_EWKB
INT1xwwww---ww-------- -- -- ----
INT2xxwww---ww-------- -- -- ----
INT4xxxww---ww-------- -- -- ----
INT8xxxxwx--ww-------- -- -- ----
DECwwwwwww-ww-------- ---- ----
DF16_...wwwwwx--ww------ -- ---- ----
DF34_...wwwwwxx-ww------ --- --- ----
CURR-------w---------- ---- ----
QUANwwwwwww-ww-------- ---- ----
FLTPxxxwwww-wx-------- ---- ----
CHAR----------ww-lw-w- - l- l -- --
SSTRING----------ww----- ---- -- ---
STRING------------w---- ---- -- ---
NUMC----------w--ll-l - -l- l -- --
DATS----------w--lx-- - --- - -- --
DATN---------------x-- - --- -- --
TIMS----------w--l--x- - --- -- --
TIMN-----------------x - - ---- --
UTCL------------------ x - ---- --
ACCP----------l--l---- - x- - -- --
CLNT------------------ --x - -- --
LANG----------l--l---- - --x -- --
UNIT------------------ ---- x- --
CUKY------------------ ---- -x --
RAW------------------ ---- -- x-
GEOM_EWKB---------------- - --- ---- -x
There are no further restrictions to note in combinations using x . The following rules apply to the other combinations:

In combinations with w, the length or the value range of the data type in the first SELECT must be long enough for all following SELECT lists. If this is not the case, a syntax check warning is raised.

In combinations using l, the lengths of the data types must match exactly.
If the length or value range of the first element does not cover the following elements in combinations with w, the value returned by the database can be too great for the data type of the element of the CDS entity. It is still possible to activate the view here, but the content may be truncated or exceptions may be raised in AB_SQL queries if a type reference is applied to the entity. This is due to the assignment rules of the INTO clause. Assignments to ABAP data objects with a sufficiently large value range, on the other hand, do not cause problems.

Associations in Views Using EXCEPT
A CDS view entity in which result sets are formed with EXCEPT can define and expose CDS associations. Such an association must be defined and exposed in the same way in all SELECT statements combined with EXCEPT. That is, CDS associations that are exposed in the respective SELECT lists must appear in all SELECT statements combined with EXCEPT and the following requirements must be met:

They have the same cardinality.

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

The fields of the association source that are listed in an ON condition must be at identical positions in the SELECT list.

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

Nesting of EXCEPT Branches
In CDS view entities, nesting of EXCEPT clauses is possible. To nest an EXCEPT clause within another EXCEPT clause, use parentheses ( ... ). A nested EXCEPT clause is merged first and its result set is then merged again with the result sets of the other EXCEPT branches.
The second example below demonstrates nesting of EXCEPT branches.

ABAP_RESTRICTIONS

Defining new CDS compositions and to-parent associations is not possible in a view using EXCEPT. Exposing CDS compositions and to-parent associations that were defined in the data source of the SELECT statement is possible.

Annotations are only allowed in the SELECT list of the first SELECT statement. In all following branches, annotations are not allowed.
ABAP_RESTR_END

Latest notes:

If the length of the value range of an element from the SELECT list of the first SELECT statement does not cover the associated elements of the following SELECT statements, it is advisable to define an appropriate type with a suitable CAST expression.

The maximum number of different SELECT statements that can be merged using EXCEPT depends on the database system. If this number is exceeded, the CDS view entity cannot be activated.

The addition ALL is not available for the set operator EXCEPT .
NON_V5_HINTS
ABAP_HINT_END

ABAP_EXAMPLE_VX5
The following CDS view entity combines the result sets of two SELECT statements using EXCEPT. It returns only unique rows returned by the first query but not by the second. In this example, it returns all flights that cost less than 2000, at the same time excluding all such flights that cost between 600 and 900.
DDLS DEMO_CDS_EXCEPT
The class CL_DEMO_CDS_EXCEPT uses SELECT to access the view and it displays the result set.
ABAP_EXAMPLE_END

ABAP_EXAMPLE_VX5 - Nesting of EXCEPT Branches
The following CDS view entity nests an EXCEPT clause within another EXCEPT clause.
DDLS DEMO_CDS_EXCEPT_NESTING
The class CL_DEMO_CDS_EXCEPT_NESTING first fills the underlying database tables and then accesses the view using SELECT. The result set consists of 9 rows.
Without nesting and the parentheses ( ) around the last two EXCEPT branches, the result set would return only 1 row.
ABAP_EXAMPLE_END