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 - Relational Operators 

sql_cond - ALL, ANY, SOME

Other versions: 7.31 | 7.40 | 7.54

Syntax


... operand operator [ALL|ANY|SOME] ( SELECT
subquery_clauses [UNION ...] ) ...

Effect

Comparison of an operand operand with the result set of a subquery. The clauses in the subquery subquery_clauses must constitute a scalar subquery. The language element UNION can be used to combine the results sets of multiple subqueries. In this case, special rules query_clauses apply for specifying clauses.

Any SQL expressions except aggregate expressions and window expressions can be specified for operand. In a HAVING clause, aggregate expressions can also be used.

operator stands for a relational operator. Results sets can be single row or multirow.


Note

When an SQL expression occurs on the left side, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.

Single Row Results Set

If the results set of the subquery defined by the clause subquery_clauses contains only one row, the comparison can be performed by specifying ALL, ANY, or SOME. The expression is true if the corresponding comparison of the value of the operand operand with the result of the scalar subquery returns "true". If the results set for the subquery contains multiple rows, a non-handleable exception is raised when the statement is executed.


Example

Reads the flights with the most passengers.

SELECT * 
       FROM sflight 
       WHERE seatsocc = ( SELECT MAX( seatsocc ) 
                                FROM sflight ) 
       INTO TABLE @DATA(flights).

Multirow Results Set

If the results set of the subquery defined by the clause subquery_clauses contains more than one row, ALL, ANY, or SOME must be specified.

  • If using ALL, the expression is true if the comparison is true for all rows in the results set of the scalar subquery.
  • If the addition ANY or SOME is used, the expression is true if it is true for at least one of the rows in the results set of the subquery.


Note

The equality operator (= or EQ) in conjunction with ANY or SOME acts like IN subquery.


Example

Reads the customer number of the customer or customers who have made the most bookings:

SELECT customid, COUNT( * ) 
       FROM sbook 
       GROUP BY customid 
       HAVING COUNT( * ) >= ALL ( SELECT COUNT( * ) 
                                       FROM sbook 
                                        GROUP BY customid ) 
       INTO (@DATA(id), @DATA(cnt)). 
  ... 
ENDSELECT.