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