Skip to content

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

sql_cond - ALL, ANY, SOMEsubquery

Other versions: 7.31 | 7.40 | 7.54

Syntax


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

Effect

These expressions can be formed with a scalar subquery. The operator stands for a relational operator. There are single-line and multiple line result sets.

Single-Line Result Set

If the result set of the subquery contains only one line, the comparison can be carried out without the specification of 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 result set for the subquery contains multiple lines, an unhandled exception occurs when the statement is executed.


Example

Reading the flight with the most passengers:

DATA wa_sflight TYPE sflight. 

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

Multiple Line Result Set

If the result set of the subsquery contains more than one line, ALL, ANY, or SOME must be specified.

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


Note

The relational operator (= or EQ) in conjunction with ANY or SOME acts like the use of IN subquery.


Example

Reading 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 
       INTO (id, cnt) 
       GROUP BY customid 
       HAVING COUNT( * ) >= ALL ( SELECT COUNT( * ) 
                                        FROM sbook 
                                        GROUP BY customid ). 
ENDSELECT.