sql_cond - subquery_clauses - ABAP Keyword Documentation

ABAP - Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Operands and Expressions → Open SQL - Conditions sql_cond → sql_cond - rel_exp for Statements →

sql_cond - subquery_clauses

Quick Reference

Syntax

... { FROM source
      FIELDS select_clause }
  | { select_clause
      FROM source }
      [WHERE sql_cond]
      [ GROUP BY group] [HAVING group_cond]
      [ ORDER BY [UP TO n ROWS [OFFSET o]]]
      [db_hints]  ...

Effect

Possible clauses and additions of a subquery in a condition sql_cond in Open SQL.

The addition USING CLIENT must not be specified n the FROM clause. The automatic client handling of the subquery is performed in accordance with the client handling of the outer statement. If specified, an addition USING CLIENT of the outer statement also applies to the subquery.

A subquery can be used within the following relational expressions sql_cond for a subquery:

operand operator [ALL|ANY|SOME] subquery

[NOT] EXISTS subquery

[NOT] IN subquery

Conditions of a subquery can themselves contain subqueries. In nested subqueries, the columns specified in the SQL conditions are searched inside out and the columns of inner subqueries obscure columns with the same name in outer subqueries.

A subquery with a single-column results set (which means that the SELECT list in result of the subquery contains only one element) is known as a scalar subquery.

A subquery that uses columns from the surrounding SELECT statement in its SQL conditions is known as a correlated subquery. It must be possible to assign the column uniquely to a data source in the surrounding SELECT statement. A correlated subquery is evaluated for each individual row of the results set in the surrounding SELECT statement.

Only a limited number of SELECT statements can be used as subqueries in an Open SQL statement. The maximum number is defined so that the Open SQL statement can be executed on all supported database systems. If statically identifiable, more than 50 SELECT statements produce a syntax error or a runtime error. This restriction is independent of whether the subqueries are nested or occur in different relational expressions of the SQL condition.

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 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 handleable exception of the class CX_SY_SQL_UNSUPPORTED_FEATURE is raised.

Notes

Subqueries cannot be used when accessing pooled tables or cluster tables.

In a correlated subquery, a projection view cannot be accessed in the surrounding SELECT statement.

If a subquery is used, the Open SQL statement bypasses table buffering.

If a query is used to access a CDS entity associated with a CDS role and for which CDS access control is not disabled using the value #NOT_ALLOWED for the annotation @AccessControl.authorizationCheck or using the addition WITH PRIVILEGED ACCESS in the FROM clause, only that data is read implicitly that matches the access condition defined in the CDS role. If data cannot be read, ABAP programs cannot distinguish whether this is due to the conditions of the SELECT statement, the conditions of the CDS entity, or an associated CDS role. If the CDS database view of a CDS view is accessed, no access control takes place.

In subqueries that are used in the WHERE condition of a DELETE or UPDATE statement, there may be no access to the database table to be changed.

If a CDS view is defined as a replacement object for a database table or database view specified as a data source of the SELECT statement of the subquery, the subquery accesses the CDS view and not the database table or the database view.

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.

Executable Example

Subquery in WHERE Condition

Continue

Subquery in WHERE Condition