Subqueries
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 ON condition 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 make sure that the subquery only returns one line by using the SINGLE expression in the SELECT 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 IN operator 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.
Examples
Correlated, non-scalar subquery:
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.
Scalar subquery:
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:
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
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.