Ansicht
Dokumentation

ABAPWITH_SUBQUERY - WITH SUBQUERY

ABAPWITH_SUBQUERY - WITH SUBQUERY

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

WITH, subquery_clauses

Short Reference





Effect

Possible clauses and additions of subqueries of a WITH statement. These clauses define the result set of a common table expression.

The result set can be used as a temporary table in subsequent subqueries and in the main query of the current WITH statement as a data source data_source.

The names of the columns of the result set are defined by the SELECT of the subquery by default. However, they can be overwritten in the WITH statement. If a union set, an intersection set, or an exception set is created in the subquery using UNION, INTERSECT, or EXCEPT the column names are determined by the SELECT list of the first SELECT statement.

If the clauses of the subquery contain dynamic tokens, the common table expression can only be used in other dynamic tokens of the WITH statement.

The addition UP TO n ROWS can only be used after ORDER BY and the addition OFFSET can only be used after UP TO n ROWS. An ORDER BY clause in a subquery is not supported by all databases. This means a syntax check warning from the extended program check can occur that can be hidden using the pragma ##db_feature_mode[limit_in_subselect_or_cte]. If this is detected at runtime on a database that does not support the pragma, a catchable exception of the class CX_SY_SQL_UNSUPPORTED_FEATURE is raised.

Notes

  • In an ABAP program, it is possible to use the method USE_FEATURES of the class CL_ABAP_DBFEATURES to check whether the current database system or a database system accessed using a secondary connection supports ORDER BY clauses in subqueries. This requires the constant LIMIT_IN_SUBSELECT_OR_CTE of this class to be passed to the method in an internal table.

Example

Subquery of a common table expression +flights with almost all possible clauses. Since the ORDER BY clause followed by the addition UP TO is not supported by all databases, a syntax warning from the extended program check is raised that can be hidden here using the pragma ##db_feature_mode[limit_in_subselect_or_cte].

Example

This example demonstrates a common table expression in which all clauses of the subquery, and the main query are specified as dynamic tokens. In the INTO clause, the addition NEW and the declaration operator @DATA(...) are used to declare a generic data reference variable that points to the result in an anonymous data object.






ABAP Short Reference   SUBST_MERGE_LIST - merge external lists to one complete list with #if... logic for R3up  
This documentation is copyright by SAP AG.

Length: 4679 Date: 20240328 Time: 174444     sap01-206 ( 49 ms )