Skip to content

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

sql_cond - subquery_clauses

Quick Reference

Other versions: 7.31 | 7.40 | 7.54

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 ABAP SQL.

In the FROM clause, the addition USING cannot be used to define client handling. Client handling of the subquery is applied in accordance with the client handling of the outer statement. If specified, an addition USING of the outer statement also applies to the subquery.

A subquery can be used within the following relational expressions sql_cond for a 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.

Only a limited number of SELECT statements can be used as subqueries in an ABAP SQL statement. The maximum number is defined so that the ABAP 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 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 handleable exception of the class CX_SY_SQL_UNSUPPORTED_FEATURE is raised.


Notes

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

  • 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 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