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
[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.
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.
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.
As well as in the above condition, you can also use scalar subqueries in further conditions.
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.
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.
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.
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:
DATA: name_tab TYPE TABLE OF scarr-carrname,
name LIKE LINE OF name_tab.
INTO TABLE name_tab
WHERE EXISTS ( select *
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.
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)
WHERE city IN ( select cityfrom
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.
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,
GROUP BY carrid connid planetype seatsmax
ORDER BY carrid connid.
WRITE: / wa-carrid,
HIDE: wa-carrid, wa-connid, wa-seatsmax.
WINDOW STARTING AT 45 3 ENDING AT 85 13.
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
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:
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.