Entering content frame

Subqueries Locate the document in its SAP Library structure

A subquery is a special SELECT statement containing a subquery within particular conditions of the WHERE or HAVING clauses. You cannot use them in the ONcondition of the FROM clause. Their syntax is:

( SELECT      result
    FROM      source
    [WHERE    condition]
    [GROUP BY fields]
    [HAVING   cond]       )

As you can see, this syntax is restricted in comparison with the full SELECT statement, since it contains no INTO or ORDER BY clause.

You can nest subqueries, that is, the WHERE and HAVING clauses of subqueries can themselves contain a subquery. When a nested subquery in the WHERE clause uses fields from the previous query, it is known as a correlated query. The subquery is then processed for each line of the database table that satisfies the previous condition.

Scalar subqueries

In a scalar subquery, the selection in the SELECT clause is restricted to one column or aggregate expression. The expression result of the SELECT clause is:

... line [agg] s

You can only enter a single field in the SELECT clause.

Subqueries in Conditions

A non-scalar subquery can only have a WHERE or HAVING clause in the [NOT] EXISTS <subquery> condition.

This condition is true if the result set of the subquery contains at least one [no] line.

Scalar Subqueries in Conditions

As well as in the above condition, you can also use scalar subqueries in further conditions.

Checking a Value of the Subquery

The following is a possible condition with scalar subqueries:

... s [NOT] IN subquery...

The condition is true if the value of <s> is [not] contained in the results set of the scalar subquery subquery.

Scalar Subqueries in Comparisons

The other conditions can all be comparisons whose operators are contained in the table for comparisons with all types in the WHERE clause. There is a difference, depending on whether the subquery selection contains one or more lines.

Single-Line Subquery

If the selection in the subquery only contains one line, use the following for the comparison:

... s operator subquery...

The value of s is compared with the value in the selection from the subquery. The condition is either true of false.

The subquery may only contain one line, otherwise a runtime error occurs. You can generate a single-line subquery by specifying the complete key in the WHERE clause.

Multiple-Line Subquery

If the selection from the subquery returns more than one line, you must write the comparison as follows:

... s operator ALL|ANY|SOME subquery...

If you use the ALL prefix, the condition is only true if the comparison is true for all lines in the subquery. If you use the ANY or SOME prefix, the condition is only true if the comparison is true for at least one line of the subquery. The equality operator (= or EQ) in conjunction with ANY or SOME has the same effect as the INoperator for checking a value.

If the selection from the subquery contains several lines and you do not use the ALL, ANY, or SOME expression, a runtime error occurs.


Correlated, non-scalar subquery:

REPORT demo_select_subquery_1.

DATA: name_tab TYPE TABLE OF scarr-carrname,
      name  LIKE LINE OF name_tab.

SELECT  carrname
  INTO  TABLE name_tab
  FROM  scarr
  WHERE EXISTS ( select  *
                   FROM  spfli
                   WHERE carrid   =  scarr~carrid AND
                         cityfrom = 'NEW YORK'        ).

LOOP AT name_tab INTO name.
  WRITE: / name.

This example selects all lines from database table SCARR for airlines that fly from New York.


Scalar subquery:

REPORT demo_select_subquery_2.

DATA: carr_id TYPE spfli-carrid VALUE 'LH',
      conn_id TYPE spfli-connid VALUE '0400'.

DATA: city  TYPE sgeocity-city,
      lati  TYPE p DECIMALS 2,
      longi TYPE p DECIMALS 2.

SELECT  SINGLE city latitude longitude
  INTO  (city, lati, longi)
  FROM  sgeocity
  WHERE city IN ( select  cityfrom
                    FROM  spfli
                    WHERE carrid = carr_id AND
                          connid = conn_id      ).

WRITE: city, lati, longi.

This example reads the latitude and longitude of the departure city of flight LH 402 from database table SGEOCITY.


Scalar subquery:

REPORT demo_select_subquery_3.

DATA: wa TYPE sflight,
      plane LIKE wa-planetype,
      seats LIKE wa-seatsmax.

SELECT     carrid connid planetype seatsmax MAX( seatsocc )
  INTO     (wa-carrid, wa-connid, wa-planetype,
            wa-seatsmax, wa-seatsocc)
  FROM     sflight
  GROUP BY carrid connid planetype seatsmax
  ORDER BY carrid connid.

  WRITE: /  wa-carrid,

  HIDE: wa-carrid, wa-connid, wa-seatsmax.




  WRITE: 'Alternative Plane Types',
         'for', wa-carrid, wa-connid.


  SELECT  planetype seatsmax
    INTO  (plane, seats)
    FROM  saplane AS plane
    WHERE seatsmax < wa-seatsmax AND
          seatsmax >= ALL ( select  seatsocc
                              FROM  sflight
                              WHERE carrid = wa-carrid AND
                                    connid = wa-connid     )
    ORDER BY seatsmax.

    WRITE: / plane, seats.


The list output, after double-clicking a line, looks like this:

This graphic is explained in the accompanying text

The detail list displays all aircraft types that have fewer seats than the currently-allocated aircraft type, but enough to carry all of the passengers currently booked on the flight.




Leaving content frame