Show TOC

SubqueriesLocate this document in the navigation 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 SELECTclause.

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 WHEREclause.

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.

Tip

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.ENDLOOP.

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

Tip

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.

Tip

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,            wa-connid,            wa-planetype,            wa-seatsmax,            wa-seatsocc.

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

ENDSELECT.

AT LINE-SELECTION.

 WINDOW STARTING AT 45 3 ENDING AT 85 13.

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

 ULINE.

 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.

 ENDSELECT.

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

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.