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

sql_cond - ALL, ANY, SOME subquery

Other versions: 7.31 | 7.40 | 7.54

Syntax

... col operator [ALL|ANY|SOME] subquery ...

Effect

These expressions can be constructed using a scalar subquery. The operator stands for a relational operator. Results sets can be single row or multirow.

Single Row Results Set

If the results set of the subquery 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 col 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 flight with the most passengers:

SELECT * 
       FROM sflight 
       WHERE seatsocc = ( SELECT MAX( seatsocc ) 
                                FROM sflight ) 
       INTO @DATA(wa_sflight). 
ENDSELECT.

Multirow Results Set

If the results set of the subquery 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:

DATA: id  TYPE sbook-customid, 
      cnt TYPE i. 

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