ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Operands and Expressions →  ABAP SQL - SQL Conditions sql_cond →  sql_cond - rel_exp for Statements →  sql_cond - subquery_clauses 

Subquery in WHERE Condition

This example demonstrates a subquery in a WHERE condition.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    SELECT carrid, connid, planetype, seatsmax,
           MAX( seatsocc ) AS seatsocc
           FROM  sflight
           GROUP BY carrid, connid, planetype, seatsmax
           ORDER BY carrid, connid
           INTO TABLE @DATA(flights).

    DATA(out) = cl_demo_output=>new( ).
    LOOP AT flights INTO DATA(wa).
      out->next_section(  |{ wa-carrid } { wa-connid }|
        )->begin_section( |{ wa-planetype }, {
                            wa-seatsmax  }, { wa-seatsocc  }| ).
      SELECT planetype, seatsmax
             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
             INTO (@DATA(plane), @DATA(seats)).
        out->write( |{ plane }, { seats }| )->end_section( ).
      ENDSELECT.
      IF sy-subrc <> 0.
        out->write( 'No alternative plane types found'
          )->end_section( ).
      ENDIF.
    ENDLOOP.
    out->display( ).

Description

For each flight, all alternative plane types are displayed that meet a connection and the current booking status but that have fewer seats than the current plane type.