sql_cond - subquery_clauses - ABAP Keyword Documentation

ABAP - Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Reads → SELECT clauses → SELECT - WHERE → WHERE - sql_cond →

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]
      [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 WHERE conditions are searched inside out, though the columns of inner subqueries conceal 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 WHERE condition, 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 WHERE condition.

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 SAP buffering.

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.

Continue

Subqueries in WHERE Conditions