Open SQL - Usage Example

This section contains examples that provide an introduction to the use of Open SQL statements, in addition to the examples given in the description of the syntax under Open SQL.

Line Specifications in the SELECT Statement

SELECT SINGLE

Example

Display free seats on Lufthansa flight 0400 28/02/2001:

DATA wa        TYPE sflight.
DATA seatsfree TYPE I.

SELECT SINGLE * FROM sflight INTO wa
       WHERE
         carrid   = 'LH '      AND
         connid   = '0400'     AND
         fldate   = '20010228'.

seatsfree = wa-seatsmax - wa-seatsocc.

WRITE: / wa-carrid, wa-connid, wa-fldate, seatsfree.

SELECT DISTINCT

Example

Display all destinations serviced by Lufthansa from Frankfurt:

DATA: target TYPE spfli-cityto.

SELECT DISTINCT cityto
       INTO target FROM spfli
       WHERE
         carrid   = 'LH '       AND
         cityfrom = 'FRANKFURT'.
  WRITE: / target.
ENDSELECT.

Example

Display number of airlines that fly to New York:

DATA   count TYPE I.

SELECT COUNT( DISTINCT carrid )
       INTO count
       FROM spfli
       WHERE
         cityto = 'NEW YORK'.
WRITE: / count.

Example

Display number of passengers, total weight, and average luggage weight for all Lufthansa flights on 28/02/01:

DATA:  count TYPE I, sum TYPE P DECIMALS 2, avg TYPE F,
       connid LIKE sbook-connid.

SELECT connid COUNT( * ) SUM( luggweight ) AVG( luggweight )
       INTO (connid, count, sum, avg)
       FROM sbook
       WHERE
         carrid   = 'LH '      AND
         fldate   = '20010228'
       GROUP BY connid.
  WRITE: / connid, count, sum, avg.
ENDSELECT.

Column IDs in the SELECT Statement

Specification Without Database Name

Example

Display a list of all customers, whose names begin with 'A':

DATA: WA_SCUSTOM TYPE SCUSTOM.

SELECT * FROM SCUSTOM INTO WA_SCUSTOM WHERE NAME LIKE 'A%'.
  WRITE: / WA_SCUSTOM-ID, WA_SCUSTOM-NAME.
ENDSELECT.

Specification With Database Name

Example

Display a list of all customers, whose names begin with 'A':

DATA: WA_SCUSTOM TYPE SCUSTOM.

SELECT * FROM SCUSTOM INTO WA_SCUSTOM
         WHERE SCUSTOM~NAME LIKE 'A%'.
  WRITE: / WA_SCUSTOM-ID, WA_SCUSTOM-NAME.
ENDSELECT.

Specification With Database Alias

Example

Display a list of all customers, whose names begin with'A':

DATA: WA_SCUSTOM TYPE SCUSTOM.

SELECT * FROM SCUSTOM AS T INTO WA_SCUSTOM WHERE T~NAME LIKE 'A%'.
  WRITE: / WA_SCUSTOM-ID, WA_SCUSTOM-NAME.
ENDSELECT.

Aggregate Function Without Alternative Name

Example

Display a list of all passengers on Lufthansa flights 0400 in 1995 with the highest booking price in each case and sorted by customer name:

DATA: SCUSTOM_WA TYPE SCUSTOM, SBOOK_WA TYPE SBOOK.

SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY
         MAX( SBOOK~LOCCURAM )
       INTO (SCUSTOM_WA-NAME, SCUSTOM_WA-POSTCODE, SCUSTOM_WA-CITY,
             SBOOK_WA-LOCCURAM)
       FROM SCUSTOM INNER JOIN SBOOK
         ON SCUSTOM~ID = SBOOK~CUSTOMID
       WHERE SBOOK~FLDATE BETWEEN '19950101' AND '19951231' AND
             SBOOK~CARRID   = 'LH '                         AND
             SBOOK~CONNID   = '0400'
       GROUP BY SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY
       ORDER BY SCUSTOM~NAME.
  WRITE: / SCUSTOM_WA-NAME, SCUSTOM_WA-POSTCODE, SCUSTOM_WA-CITY,
           SBOOK_WA-LOCCURAM.
ENDSELECT.

Aggregate Function With Alternative Name

Example

Display a list of all passengers on Lufthansa flights 0400 in 1995 with the average booking price in each case, sorted by price and customer name:

DATA: SCUSTOM_WA TYPE SCUSTOM, SBOOK_WA TYPE SBOOK.

SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY
         AVG( SBOOK~LOCCURAM ) AS AVG
       INTO (SCUSTOM_WA-NAME, SCUSTOM_WA-POSTCODE, SCUSTOM_WA-CITY,
             SBOOK_WA-LOCCURAM)
       FROM SCUSTOM INNER JOIN SBOOK
         ON SCUSTOM~ID = SBOOK~CUSTOMID
       WHERE SBOOK~FLDATE BETWEEN '19950101' AND '19951231' AND
             SBOOK~CARRID   = 'LH '                         AND
             SBOOK~CONNID   = '0400'
      GROUP BY SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY
      ORDER BY AVG DESCENDING SCUSTOM~NAME.
  WRITE: / SCUSTOM_WA-NAME, SCUSTOM_WA-POSTCODE, SCUSTOM_WA-CITY,
           SBOOK_WA-LOCCURAM.
ENDSELECT.

Aggregate Function max

Example

Display a list of all passengers on Lufthansa flights 0400 in 2001 with the highest booking price in each case and sorted by customer name:

DATA: name     TYPE scustom-name,
      postcode TYPE scustom-postcode,
      city     TYPE scustom-city,
      max      TYPE sbook-loccuram.

SELECT scustom~name scustom~postcode scustom~city
         MAX( sbook~loccuram )
       INTO (name, postcode, city, max)
       FROM scustom INNER JOIN sbook
         ON scustom~id = sbook~customid
       WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND
             sbook~carrid   = 'LH '                         AND
             sbook~connid   = '0400'
       GROUP BY scustom~name scustom~postcode scustom~city
       ORDER BY scustom~name.
  WRITE: / name, postcode, city, max.
ENDSELECT.

Aggregate Function min

Example

Display a list of all passengers on Lufthansa flights 0400 in 2001 with the lowest booking price in each case and sorted by customer name:

DATA: name     TYPE scustom-name,
      postcode TYPE scustom-postcode,
      city     TYPE scustom-city,
      min      TYPE sbook-loccuram.

SELECT scustom~name scustom~postcode scustom~city
         MIN( sbook~loccuram )
       INTO (name, postcode, city, min)
       FROM scustom INNER JOIN sbook
         ON scustom~id = sbook~customid
       WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND
             sbook~carrid   = 'LH '                         AND
             sbook~connid   = '0400'
       GROUP BY scustom~name scustom~postcode scustom~city
       ORDER BY scustom~name.
  WRITE: / name, postcode, city, min.
ENDSELECT.

Aggregate Function avg

Example

Display a list of all passengers on Lufthansa flights 0400 in 2001 with the average booking price in each case, sorted by customer name:

DATA: name     TYPE scustom-name,
      postcode TYPE scustom-postcode,
      city     TYPE scustom-city,
      average  TYPE sbook-loccuram.

SELECT scustom~name scustom~postcode scustom~city
         AVG( sbook~loccuram ) AS avg
       INTO (name, postcode, city, average)
       FROM scustom INNER JOIN sbook
         ON scustom~id = sbook~customid
       WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND
             sbook~carrid   = 'LH '                         AND
             sbook~connid   = '0400'
       GROUP BY scustom~name scustom~postcode scustom~city
       ORDER BY avg DESCENDING scustom~name.
  WRITE: / name, postcode, city, average.
ENDSELECT.

Aggregate Function sum

Example

Display a list of all passengers on Lufthansa flights 0400 in 2001 with the booking price sum in each case and sorted by customer name:

DATA: name     TYPE scustom-name,
      postcode TYPE scustom-postcode,
      city     TYPE scustom-city,
      sum      TYPE sbook-loccuram.

SELECT scustom~name scustom~postcode scustom~city
         SUM( sbook~loccuram )
       INTO (name, postcode, city, sum)
       FROM scustom INNER JOIN sbook
         ON scustom~id = sbook~customid
       WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND
             sbook~carrid   = 'LH '                         AND
             sbook~connid   = '0400'
       GROUP BY scustom~name scustom~postcode scustom~city
       ORDER BY scustom~name.
  WRITE: / name, postcode, city, sum.
ENDSELECT.

Aggregate Function count

Example

Display a list of all passengers on Lufthansa flights 0400 in 2001 with the number of different booking prices in each case and sorted by customer name:

DATA: name     TYPE scustom-name,
      postcode TYPE scustom-postcode,
      city     TYPE scustom-city,
      count    TYPE I.

SELECT scustom~name scustom~postcode scustom~city
         COUNT( DISTINCT sbook~loccuram )
       INTO (name, postcode, city, count)
       FROM scustom INNER JOIN sbook
         ON scustom~id = sbook~customid
       WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND
             sbook~carrid   = 'LH '                         AND
             sbook~connid   = '0400'
       GROUP BY scustom~name scustom~postcode scustom~city
       ORDER BY scustom~name.
  WRITE: / name, postcode, city, count.
ENDSELECT.

Aggregate Function count( * )

Example

Display a list of all passengers on Lufthansa flights 0400 in 2001 with the number of bookings in each case and sorted by customer name:

DATA: name     TYPE scustom-name,
      postcode TYPE scustom-postcode,
      city     TYPE scustom-city,
      count    TYPE I.

SELECT scustom~name scustom~postcode scustom~city COUNT( * )
       INTO (name, postcode, city, count)
       FROM scustom INNER JOIN sbook
         ON scustom~id = sbook~customid
       WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND
             sbook~carrid   = 'LH '                         AND
             sbook~connid   = '0400'
       GROUP BY scustom~name scustom~postcode scustom~city
       ORDER BY scustom~name.
  WRITE: / name, postcode, city, count.
ENDSELECT.

Dynamic Column Specification

Example

Display all Lufthansa flight routes:

DATA: wa   TYPE spfli,
      ftab TYPE TABLE OF STRING.

APPEND 'CITYFROM' TO ftab.
APPEND 'CITYTO'   TO ftab.

SELECT DISTINCT (ftab)
       FROM spfli
       INTO CORRESPONDING FIELDS OF wa
       WHERE
         carrid   = 'LH'.
  WRITE: / wa-cityfrom, wa-cityto.
ENDSELECT.

Database Tables in the SELECT Statement

Client Specification

Example

Display a list of all customers in client 3:

DATA: WA_SCUSTOM TYPE SCUSTOM.

SELECT * FROM SCUSTOM CLIENT SPECIFIED INTO WA_SCUSTOM
         WHERE MANDT = '003'.
  WRITE: / WA_SCUSTOM-ID, WA_SCUSTOM-NAME.
ENDSELECT.

Limited Line Number

Example

Display list of the 3 business partners with the highest discount rates:

DATA: WA_SCUSTOM TYPE SCUSTOM.

SELECT * FROM SCUSTOM INTO WA_SCUSTOM UP TO 3 ROWS

         WHERE CUSTTYPE = 'B'
         ORDER BY DISCOUNT DESCENDING.
  WRITE: / WA_SCUSTOM-ID, WA_SCUSTOM-NAME,
           WA_SCUSTOM-DISCOUNT.
ENDSELECT.

Inner Join

Example

Inner join between table 1 and table 2, in which the column D of both tables is equated in the join condition:

Table 1                      Table 2
|----|----|----|----|        |----|----|----|----|----|
| A  | B  | C  | D  |        | D  | E  | F  | G  | H  |
|----|----|----|----|        |----|----|----|----|----|
| a1 | b1 | c1 | 1  |        | 1  | e1 | f1 | g1 | h1 |
| a2 | b2 | c2 | 1  |        | 3  | e2 | f2 | g2 | h2 |
| a3 | b3 | c3 | 2  |        | 4  | e3 | f3 | g3 | h3 |
| a4 | b4 | c4 | 3  |        |----|----|----|----|----|
|----|----|----|----|

                    \        /
                     \      /
                      \    /
                       \  /
                        \/
    Inner Join
    |----|----|----|----|----|----|----|----|----|
    | A  | B  | C  | D  | D  | E  | F  | G  | H  |
    |----|----|----|----|----|----|----|----|----|
    | a1 | b1 | c1 | 1  | 1  | e1 | f1 | g1 | h1 |
    | a2 | b2 | c2 | 1  | 1  | e1 | f1 | g1 | h1 |
    | a4 | b4 | c4 | 3  | 3  | e2 | f2 | g2 | h2 |
    |----|----|----|----|----|----|----|----|----|

Example

Display a list of all flights with available seats from Frankfurt to New York between the 10th and 20th of September 1997:

DATA: DATE   LIKE SFLIGHT-FLDATE,
      CARRID LIKE SFLIGHT-CARRID,
      CONNID LIKE SFLIGHT-CONNID.

SELECT F~CARRID F~CONNID F~FLDATE
    INTO (CARRID, CONNID, DATE)
    FROM SFLIGHT AS F INNER JOIN SPFLI AS P
           ON F~CARRID = P~CARRID AND
              F~CONNID = P~CONNID
    WHERE P~CITYFROM = 'FRANKFURT'
      AND P~CITYTO   = 'NEW YORK'
      AND F~FLDATE BETWEEN '19970910' AND '19970920'
      AND F~SEATSOCC < F~SEATSMAX.
  WRITE: / DATE, CARRID, CONNID.
ENDSELECT.

Example

Display a list of all flights with available seats from Frankfurt to New York between the 10th and 20th of September 1997:

DATA: DATE   LIKE SFLIGHT-FLDATE,
      CARRID LIKE SFLIGHT-CARRID,
      CONNID LIKE SFLIGHT-CONNID.

SELECT F~CARRID F~CONNID F~FLDATE
    INTO (CARRID, CONNID, DATE)
    FROM SFLIGHT AS F INNER JOIN SPFLI AS P
           ON F~CARRID = P~CARRID
    WHERE F~CONNID = P~CONNID
      AND P~CITYFROM = 'FRANKFURT'
      AND P~CITYTO   = 'NEW YORK'
      AND F~FLDATE BETWEEN '19970910' AND '19970920'
      AND F~SEATSOCC < F~SEATSMAX.
  WRITE: / DATE, CARRID, CONNID.
ENDSELECT.

Example

Example of a JOIN with more than two tables: Select all flights with available seats from Frankfurt to New York between the 10th and 20th of September 1997 and display them with the airline:

DATA: BEGIN OF WA,
        FLIGHT TYPE SFLIGHT,
        PFLI   TYPE SPFLI,
        CARR   TYPE SCARR,
      END OF WA.

SELECT * INTO WA
    FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P
             ON F~CARRID = P~CARRID AND
                F~CONNID = P~CONNID )
           INNER JOIN SCARR AS C
             ON F~CARRID = C~CARRID
    WHERE P~CITYFROM = 'FRANKFURT'
      AND P~CITYTO   = 'NEW YORK'
      AND F~FLDATE BETWEEN '19970910' AND '19970920'
      AND F~SEATSOCC < F~SEATSMAX.
  WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID,
           WA-FLIGHT-CONNID.
ENDSELECT.

Outer Join

Example

Left outer join between table 1 and table 2, in which the column D of both tables is equated in the join condition:

Tabelle 1                    Tabelle 2
|----|----|----|----|        |----|----|----|----|----|
| A  | B  | C  | D  |        | D  | E  | F  | G  | H  |
|----|----|----|----|        |----|----|----|----|----|
| a1 | b1 | c1 | 1  |        | 1  | e1 | f1 | g1 | h1 |
| a2 | b2 | c2 | 1  |        | 3  | e2 | f2 | g2 | h2 |
| a3 | b3 | c3 | 2  |        | 4  | e3 | f3 | g3 | h3 |
| a4 | b4 | c4 | 3  |        |----|----|----|----|----|
|----|----|----|----|

                    \        /
                     \      /
                      \    /
                       \  /
                        \/
    Left Outer Join
    |----|----|----|----|----|----|----|----|----|
    | A  | B  | C  | D  | D  | E  | F  | G  | H  |
    |----|----|----|----|----|----|----|----|----|
    | a1 | b1 | c1 | 1  | 1  | e1 | f1 | g1 | h1 |
    | a2 | b2 | c2 | 1  | 1  | e1 | f1 | g1 | h1 |
    | a3 | b3 | c3 | 2  |NULL|NULL|NULL|NULL|NULL|
    | a4 | b4 | c4 | 3  | 3  | e2 | f2 | g2 | h2 |
    |----|----|----|----|----|----|----|----|----|

Example

Display a list of all customers with all their bookings (if any) for October 15th 1997:

DATA: CUSTOMER TYPE SCUSTOM,
      BOOKING  TYPE SBOOK.

SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY
       SBOOK~FLDATE SBOOK~CARRID SBOOK~CONNID SBOOK~BOOKID
       INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,
             BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,
             BOOKING-BOOKID)
       FROM SCUSTOM LEFT OUTER JOIN SBOOK
         ON SCUSTOM~ID = SBOOK~CUSTOMID AND
            SBOOK~FLDATE = '19971015'
       ORDER BY SCUSTOM~NAME SBOOK~FLDATE.
  WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,
           BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,
           BOOKING-BOOKID.
ENDSELECT.

Dynamic Specification

Example

Display a list of all customers:

DATA  tabname TYPE c  LENGTH 10.
DATA: BEGIN OF wa,
        id   TYPE scustom-id,
        name TYPE scustom-name,
      END OF wa.

tabname = 'SCUSTOM'.
SELECT id name INTO CORRESPONDING FIELDS OF wa FROM (tabname).
  WRITE: / wa-id, wa-name.
ENDSELECT.

Example

Display a list of all flight connections with the name of the airline and the flight number; a join is created dynamically at runtime:

CONSTANTS: flight_tab_name TYPE c LENGTH 30 VALUE 'SPFLI'.

DATA: from_clause TYPE STRING.
DATA: BEGIN OF wa,
        name(20) TYPE C,
        connid   TYPE spfli-connid,
      END OF wa.

CONCATENATE flight_tab_name ' AS t1'
            ' JOIN scarr AS t2 ON t1~carrid = t2~carrid'
   INTO from_clause.

SELECT t1~connid t2~carrname AS name
    FROM (from_clause)
    INTO CORRESPONDING FIELDS OF wa.
  WRITE: / wa-name, wa-connid.
ENDSELECT.

Target Area in the SELECT Statement

Work Area Specification

Example

Display a list of all airlines (with ID and name):

DATA: wa TYPE scarr.

SELECT * INTO WA FROM scarr.
  WRITE: / wa-carrid, wa-carrname.
ENDSELECT.

Example

Display a list of all airlines (with ID and name):

DATA: BEGIN OF wa1,
        client   TYPE scarr-mandt,
        carrid   TYPE scarr-carrid,
        carrname TYPE scarr-carrname,
        url      TYPE scarr-url,
        rest(100),
      END OF wa1.

SELECT * INTO wa1 FROM SCARR.
  WRITE: / wa1-carrid, wa1-carrname.
ENDSELECT.

Example

Display a list of all airlines (with ID and name):

DATA: BEGIN OF wa2,
        carrid   TYPE scarr-carrid,
        carrname TYPE scarr-carrname,
      END OF wa2.

SELECT carrid carrname
       INTO wa2
       FROM scarr.
  WRITE: / wa2-carrid, wa2-carrname.
ENDSELECT.

Single Data Objects

Example

Display a list of all airlines (with ID and name):

DATA: carrier TYPE scarr.
DATA: tabname(10).

tabname = 'SCARR'.

SELECT *
       INTO CORRESPONDING FIELDS OF carrier
       FROM (tabname).
  WRITE: / carrier-carrid, carrier-carrname.
ENDSELECT.

Internal Table

Example

Display a list of all airlines (with ID and name):

DATA:  ITAB TYPE STANDARD TABLE OF scarr WITH NON-UNIQUE
                 DEFAULT KEY INITIAL SIZE 100,
       wa_itab TYPE scarr.

SELECT * INTO TABLE itab FROM scarr.
LOOP AT itab INTO wa_itab.
  WRITE: / wa_itab-carrid, wa_itab-carrname.
ENDLOOP.

Example

Display a list of all airlines (with ID and name):

DATA: itab TYPE STANDARD TABLE OF SCARR WITH NON-UNIQUE
                 DEFAULT KEY INITIAL SIZE 10.
FIELD-SYMBOLS: <FS> TYPE scarr.

SELECT * INTO TABLE itab PACKAGE SIZE 20 FROM scarr.
  LOOP AT itab ASSIGNING <FS>.
    WRITE: / <FS>-carrid, <FS>-carrname.
  ENDLOOP.
ENDSELECT.

WHERE Conditions

Comparison Operators

Examples

Select all Lufthansa flight connections:

DATA: WA_SPFLI TYPE SPFLI.

SELECT * FROM SPFLI INTO WA_SPFLI
    WHERE CARRID = 'LH'.
  WRITE: / WA_SPFLI-CITYFROM, WA_SPFLI-CITYTO.
ENDSELECT.

Select all flights that are not fully booked:

DATA: WA_SFLIGHT TYPE SFLIGHT.

SELECT * FROM SFLIGHT INTO WA_SFLIGHT
    WHERE SFLIGHT~SEATSOCC LT SFLIGHT~SEATSMAX.
  WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID,
           WA_SFLIGHT-FLDATE.
ENDSELECT.

Select the flights with the most passengers:

DATA: WA_SFLIGHT TYPE SFLIGHT.

SELECT * FROM SFLIGHT INTO WA_SFLIGHT
    WHERE SEATSOCC = ( SELECT MAX( SEATSOCC ) FROM SFLIGHT ).
    WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID,

             WA_SFLIGHT-FLDATE.
ENDSELECT.

Incorrect Null Value Query

Example

None of the following commands will find a line in which the field TELEPHONE contains the value NULL:

DATA: WA_SCUSTOM TYPE SCUSTOM.

SELECT * FROM SCUSTOM INTO WA_SCUSTOM WHERE TELEPHONE = ' '.
ENDSELECT.
SELECT * FROM SCUSTOM INTO WA_SCUSTOM WHERE TELEPHONE <> ' '.
ENDSELECT.
SELECT * FROM SCUSTOM INTO WA_SCUSTOM WHERE NOT TELEPHONE = ' '.
ENDSELECT.

Interval

Example

Select all passenger planes with 200 to 250 seats:

DATA: WA_SAPLANE TYPE SAPLANE,
      LOW  TYPE I VALUE 200,
      HIGH TYPE I VALUE 250.

SELECT * FROM SAPLANE INTO WA_SAPLANE
    WHERE SEATSMAX BETWEEN LOW AND HIGH.
  WRITE: / WA_SAPLANE-PLANETYPE.
ENDSELECT.

Character Strings

Examples

Select all customers whose names begin with 'M':

DATA SCUSTOM_WA TYPE SCUSTOM.

SELECT ID NAME FROM SCUSTOM
    INTO CORRESPONDING FIELDS OF SCUSTOM_WA
    WHERE NAME LIKE 'M%'.
  WRITE: / SCUSTOM_WA-ID, SCUSTOM_WA-NAME.
ENDSELECT.

Select all customers whose names contain 'huber':

DATA SCUSTOM_WA TYPE SCUSTOM.

SELECT ID NAME FROM SCUSTOM
    INTO CORRESPONDING FIELDS OF SCUSTOM_WA
    WHERE NAME LIKE '%huber%'.
  WRITE: / SCUSTOM_WA-ID, SCUSTOM_WA-NAME.
ENDSELECT.

Select all customers whose names do not have an 'n' as the second letter:

DATA SCUSTOM_WA TYPE SCUSTOM.

SELECT ID NAME FROM SCUSTOM
    INTO CORRESPONDING FIELDS OF SCUSTOM_WA
    WHERE NAME NOT LIKE '_n%'.
  WRITE: / SCUSTOM_WA-ID, SCUSTOM_WA-NAME.
ENDSELECT.

Example

Select all customers whose names begin with '100%':

DATA SCUSTOM_WA TYPE SCUSTOM.

SELECT ID NAME FROM SCUSTOM
    INTO CORRESPONDING FIELDS OF SCUSTOM_WA
    WHERE NAME LIKE '100#%' ESCAPE '#'.
  WRITE: / SCUSTOM_WA-ID, SCUSTOM_WA-NAME.
ENDSELECT.

Value List

Example

Select all destinations serviced by Lufthansa or Singapore Airlines from Frankfurt:

DATA   TARGET LIKE SPFLI-CITYTO.

SELECT DISTINCT CITYTO
    INTO TARGET FROM SPFLI
    WHERE CARRID   IN ('AA', 'LH', 'SQ') AND
          CITYFROM = 'FRANKFURT'.
  WRITE: / TARGET.
ENDSELECT.

Subquery

Example

Select the flights with the most passengers:

DATA: WA_SFLIGHT TYPE SFLIGHT.

SELECT * FROM SFLIGHT INTO WA_SFLIGHT
    WHERE SEATSOCC IN ( SELECT MAX( SEATSOCC ) FROM SFLIGHT ).
  WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID,
           WA_SFLIGHT-FLDATE.
ENDSELECT.

Example

Select the flights for which there is at least one booking:

DATA: WA_SFLIGHT TYPE SFLIGHT.

SELECT * FROM SFLIGHT AS F INTO WA_SFLIGHT
   WHERE EXISTS ( SELECT * FROM SBOOK
                     WHERE CARRID = F~CARRID
                       AND CONNID = F~CONNID
                       AND FLDATE = F~FLDATE ).
  WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID,
           WA_SFLIGHT-FLDATE.
ENDSELECT.

Example

Select all flights with available seats from Frankfurt to New York between 01/01/1999 and 31/03/1999:

DATA: WA_SFLIGHT TYPE SFLIGHT.

SELECT * FROM SFLIGHT AS F INTO WA_SFLIGHT
    WHERE SEATSOCC < F~SEATSMAX
      AND EXISTS ( SELECT * FROM SPFLI
                     WHERE CARRID = F~CARRID
                       AND CONNID = F~CONNID
                       AND CITYFROM = 'FRANKFURT'
                       AND CITYTO = 'NEW YORK' )
      AND FLDATE BETWEEN '19990101' AND '19990331'.
  WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID,
           WA_SFLIGHT-FLDATE.
ENDSELECT.

Example

This example illustrates the use of ALL. A list is displayed containing the customer number(s) of the customer(s) that have/has made the most bookings:

DATA: ID TYPE SBOOK-CUSTOMID, CNT TYPE I.

SELECT CUSTOMID COUNT( * ) AS C FROM SBOOK
    INTO (ID, CNT)
    GROUP BY CUSTOMID
    HAVING COUNT( * ) >=
      ALL ( SELECT COUNT( * ) FROM SBOOK GROUP BY CUSTOMID ).
  WRITE: / ID, CNT.
ENDSELECT.

Selection Table

Example

Select the customer numbers:

• '10000000' to '19999999',
  
• '01104711'
  
• All customer numbers smaller than or equal to '90000000',
  
  
  

But do not select the customer numbers

• '10000810' to '10000815',
  
• '10000911'
  
• All customer numbers whose fifth number is a '5':
  
  
  

DATA: WA_SCUSTOM TYPE SCUSTOM.

SELECT-OPTIONS: R FOR WA_SCUSTOM-ID.
* RANGES:       R FOR WA_SCUSTOM-ID.

* R filled as follows (the order
* of the lines is irrelevant):
*
* SIGN  OPTION  LOW       HIGH
* --------------------------------
* I     EQ      01104711
* I     BT      10000000  19999999
* I     GE      90000000
* E     EQ      10000911
* E     BT      10000810  10000815
* E     CP      ++++5*
*
* This results in the following condition
*
* ( ID = '01104711'                        OR
*   ID BETWEEN '10000000' AND '19999999'   OR
*   ID >= '90000000' )                       AND
* ID <> '10000911'                           AND
* ID NOT BETWEEN '10000810' AND '10000815'   AND
* ID NOT LIKE '____5%'
*

SELECT * FROM SCUSTOM INTO WA_SCUSTOM WHERE ID IN R.
  ...
ENDSELECT.

Null-Wert

Example

Select all customers for which no telephone number is specified.

DATA: WA_SCUSTOM TYPE SCUSTOM.

SELECT * FROM SCUSTOM INTO WA_SCUSTOM WHERE TELEPHONE IS NULL.
ENDSELECT.

NOT

Example

Select the customers with customer numbers that do not live in postal code area 68:

... WHERE  NOT POSTCODE LIKE '68%'

AND

Example

Select the customers with customer numbers that are smaller than '01000000' and do not live in postal code area 68:

... WHERE ID < '01000000'
          AND NOT
          POSTCODE LIKE '68%'

OR

Example

Select the customers with customer numbers that are smaller than '01000000' or higher than '02000000':

... WHERE ID < '01000000' OR
          ID > '02000000'.

Select the customers with customer numbers that are smaller than '01000000' or higher than '02000000' and do not live in postal code area 68 or 69:

... WHERE ( ID < '01000000' OR ID > '02000000' )
          AND NOT
          ( POSTCODE LIKE '68%' OR POSTCODE LIKE '69%' )

FOR ALL ENTRIES

Example

Display available seats on flights on 28/02/2001.

TYPES: BEGIN OF ftab_type,
         carrid TYPE sflight-carrid,
         connid TYPE sflight-connid,
       END OF ftab_type.

DATA:  ftab TYPE STANDARD TABLE OF ftab_type WITH
                 NON-UNIQUE DEFAULT KEY INITIAL SIZE 10,
       free TYPE I,
       wa_sflight TYPE sflight.

* ftab filled as follows:
*
* CARRID  CONNID
* --------------
* LH      2415
* SQ      0026
* LH      0400

SELECT * FROM sflight INTO wa_sflight
    FOR ALL ENTRIES IN ftab
    WHERE CARRID = ftab-carrid AND
          CONNID = ftab-connid AND
          fldate = '20010228'.
  free = wa_sflight-seatsocc - wa_sflight-seatsmax.
  WRITE: / wa_sflight-carrid, wa_sflight-connid, free.
ENDSELECT.

* The command then works like:

SELECT DISTINCT * FROM sflight INTO wa_sflight
    WHERE ( carrid = 'LH'   AND
            connid = '2415' AND
            fldate = '20010228' ) OR
          ( carrid = 'SQ'   AND
            connid = '0026' AND
            fldate = '20010228' ) OR
          ( carrid = 'LH'   AND
            connid = '0400' AND
            fldate = '20010228' ).
  free = wa_sflight-seatsocc - wa_sflight-seatsmax.
  WRITE: / wa_sflight-carrid, wa_sflight-connid, free.
ENDSELECT.

Dynamic Condition

Example

Display all flights on a certain date after specifying the date and the airline.

PARAMETERS: airline(2) TYPE C,
            date       TYPE D.
DATA: where_clause TYPE STRING,
      connid       TYPE sflight-connid.

CONCATENATE     'carrid = ''' airline ''''
            ' AND fldate = ''' date '''' INTO where_clause.

SELECT connid FROM sflight INTO connid
  WHERE (where_clause).
  WRITE: / date, airline, connid.
ENDSELECT.

Grouping Lines in the SELECT Statement

Static Specification

Example

Display the number of passengers, the total weight and average weight of luggage for all Lufthansa flights. am 28.02.2001.

DATA:  count TYPE I, sum TYPE P DECIMALS 2, avg TYPE F.
DATA:  connid TYPE sbook-connid.

SELECT connid COUNT( * ) SUM( luggweight ) AVG( luggweight )
       INTO (connid, count, sum, avg)
       FROM sbook
       WHERE
         carrid   = 'LH'       AND
         fldate   = '20010228'
       GROUP BY connid.
  WRITE: / connid, count, sum, avg.
ENDSELECT.

Dynamic Specification

Example

Display all Lufthansa departure cities with number of destinations.

DATA:   BEGIN OF wa.
          INCLUDE STRUCTURE spfli.
DATA:     count TYPE I.
DATA:   END OF wa.
DATA:   field_list    TYPE STRING,
        group_by_list TYPE STRING,
        count TYPE I.

field_list    = 'CITYFROM COUNT( * ) AS COUNT'.
group_by_list = 'CITYFROM'.

SELECT DISTINCT (field_list)
       INTO CORRESPONDING FIELDS OF wa
       FROM spfli
       WHERE
         carrid   = 'LH'
       GROUP BY (group_by_list).
  WRITE: / wa-cityfrom, wa-count.
ENDSELECT.

Limiting Grouped Lines in the SELECT Statement

Static Specification

Example

Display the number of passengers and average luggage weight for all Lufthansa flights on 28/02/01 for which the average luggage weight was more than 20 kg.

DATA:  count  TYPE I,

       avg    TYPE F,
       connid TYPE sbook-connid.

SELECT CONNID COUNT( * ) avg( LUGGWEIGHT )
       INTO (connid, count, avg)
       FROM sbook
       WHERE
         carrid   = 'LH'       AND
         fldate   = '20010228'
       GROUP BY connid
       HAVING AVG( luggweight ) > '20.0'.
  WRITE: / connid, count, avg.
ENDSELECT.

Dynamic Specification

Example

After assigning 'cityfrom' and 'sbook' to the parameter comp or tabname, all departure cities with more than 3 destinations are displayed.

PARAMETERS: comp(80)    TYPE C,
            tabname(80) TYPE C.
DATA:   dref      TYPE REF TO DATA,
        long_name TYPE STRING,
        count     TYPE I,
        fieldlist TYPE STRING,
        condition TYPE STRING.
FIELD-SYMBOLS: <fs>.

CONCATENATE tabname '-' comp INTO long_name.
CREATE DATA dref TYPE (long_name).
ASSIGN dref->* TO <fs>.

CONCATENATE comp ' count(*)' INTO fieldlist.
condition = 'count(*) > 3'.

SELECT DISTINCT (fieldlist)
       INTO (<fs>, count)
       FROM (tabname)
       GROUP BY (comp)
       HAVING (condition).
  WRITE: / <fs>, count.
ENDSELECT.

Sorting Lines in the SELECT Statement

Sorting by Primary Key

Example

Display passenger list for Lufthansa flight 0400 on 28/02/2001.

DATA: wa_sbook TYPE sbook.

SELECT * FROM sbook INTO wa_sbook
         WHERE
           carrid   = 'LH '      AND
           connid   = '0400'     AND
           fldate   = '20010228'
         ORDER BY PRIMARY KEY.
  WRITE: / wa_sbook-bookid,     wa_sbook-customid,
           wa_sbook-custtype,   wa_sbook-smoker,
           wa_sbook-luggweight, wa_sbook-wunit,
           wa_sbook-invoice.
ENDSELECT.

Static Column Specification

Example

Display Lufthansa flights from 27/02/2001 to 05/03/2001, sorted by aircraft type and number of occupied seats.

DATA: wa_sflight TYPE sflight.

SELECT * FROM sflight INTO wa_sflight
         WHERE carrid = 'LH' AND
               fldate BETWEEN '20010227' AND '20010305'
         ORDER BY planetype ASCENDING seatsocc DESCENDING.
  WRITE: / wa_sflight-planetype, wa_sflight-seatsocc,
           wa_sflight-connid, wa_sflight-fldate.
ENDSELECT.

Dynamic Column Specification

Example

After entering 'cityfrom' or 'cityto', all departure cities or destinations serviced by Lufthansa are displayed with the number of destinations.

PARAMETERS: comp(80).
DATA:   dref TYPE REF TO DATA,
        long_name TYPE STRING,
        name TYPE STRING,
        ftab TYPE TABLE OF STRING,
        count TYPE I.
FIELD-SYMBOLS: <fs>.

name = 'SPFLI'.
CONCATENATE name '-' comp INTO long_name.
CREATE DATA dref TYPE (long_name).
ASSIGN dref->* TO <fs>.

APPEND comp TO ftab.
APPEND 'COUNT( * ) AS COUNT' TO ftab.

SELECT DISTINCT (ftab)
       INTO (<fs>, count)
       FROM (name)
       WHERE
         carrid   = 'LH'
       GROUP BY (comp)
       ORDER BY (comp).

  WRITE: / <fs>, count.
ENDSELECT.

Explicit Cursor Handling

Opening a Cursor

Example

Open the database cursor C1 on the database table SFLIGHT for a SELECT statement.

DATA: C1 TYPE CURSOR.

OPEN CURSOR C1 FOR
     SELECT * FROM SFLIGHT WHERE CARRID = 'LH '.

Reading a Result Set

Example

Display the passenger list of Lufthansa flight 0400 on 28/02/2001.

DATA:   c  TYPE CURSOR,
        wa TYPE sbook.

OPEN CURSOR c FOR SELECT * FROM sbook
  WHERE
    carrid   = 'LH '      AND
    connid   = '0400'     AND
    fldate   = '20010228'
  ORDER BY PRIMARY KEY.

DO.
  FETCH NEXT CURSOR c INTO wa.
  IF SY-SUBRC <> 0.
    CLOSE CURSOR C. EXIT.
  ENDIF.
  WRITE: / wa-bookid, wa-customid,   wa-custtype,
           wa-smoker, wa-luggweight, wa-wunit,
           wa-invoice.
ENDDO.

CLOSE CURSOR c.

Inserting Lines Using INSERT

Automatic Client Handling

Example

Add the customer Robinson in the current client.

DATA: wa TYPE scustom.
wa-id        = '12400177'.
wa-name      = 'Robinson'.
wa-postcode  = '69542'.
wa-city      = 'Heidelberg'.
wa-custtype  = 'P'.
wa-discount  = '003'.
wa-telephone = '06201/44889'.

INSERT INTO scustom VALUES wa.

Explicit Client Specification

Example

Add the customer Robinson in client 2.

DATA: wa TYPE scustom.
wa-mandt     = '002'.
wa-id        = '12400177'.
wa-name      = 'Robinson'.
wa-postcode  = '69542'.
wa-city      = 'Heidelberg'.
wa-custtype  = 'P'.
wa-discount  = '003'.
wa-telephone = '06201/44889'.

INSERT scustom CLIENT SPECIFIED FROM wa.

Changing Lines Using UPDATE

Column Specification

Example

Change the customer discount to 3 per cent for all customers (in the current client).

UPDATE scustom SET discount = '003'.

Example

The same example, using a dynamic SET condition:

DATA: tabname     TYPE STRING,
      set_clause  TYPE STRING.

tabname    = 'SCUSTOM'.
set_clause = 'DISCOUNT = ''003'' '.

UPDATE (tabname) SET (set_clause).

Example

You should not use the colon-comma logic, since it could result in an entire database table being changed if it is used incorrectly. In the following code section, not one single statement updates the discount and telephone number of the customer with customer number '00017777'. Instead, there are two statements; the first changes the discount for all customers and the second changes the telephone number of the customer with customer number '00017777'.

* FALSCH * WRONG * FALSCH * WRONG * FALSCH * WRONG *
* UPDATE SCUSTOM SET:  DISCOUNT  = '003',
*                      TELEPHONE = '0621/444444'
*                WHERE ID        = '00017777'.

WHERE Condition

Example

Increase the number of occupied seats on flight 0400 on 28/02/2001 by 3 seats (in the current client).

UPDATE sflight SET   seatsocc = seatsocc + 3
               WHERE carrid   = 'LH'   AND
                     connid   = '0400' AND
                     fldate   = '20010228'.

WHERE Condition with Client Specification

Example

Increase the number of occupied seats on flight 0400 on 28/02/2001 by 3 seats (in client 2).

UPDATE sflight CLIENT SPECIFIED
               SET   seatsocc = seatsocc + 3
               WHERE mandt    = '002'  AND
                     carrid   = 'LH'   AND
                     connid   = '0400' AND
                     fldate   = '20010228'.

Specifying a Work Area

Example

Change the telephone number of the customer with customer number '12400177' in the current client.

DATA   wa TYPE scustom.

SELECT SINGLE * FROM scustom INTO wa
  WHERE id = '12400177'.

wa-telephone = '06201/44889'.
UPDATE scustom FROM wa.

Example

Change the discount of the customer with customer number '00017777' to 3 per cent (in the current client).

DATA: wa TYPE scustom.

SELECT SINGLE * FROM scustom INTO wa
  WHERE id = '00017777'.

wa-discount = '003'.
UPDATE scustom FROM wa.

Inserting or Changing Lines Using MODIFY

Example

Add or change data for customer Robinson in the current client:

DATA: wa TYPE scustom.
wa-id        = '12400177'.
wa-name      = 'Robinson'.
wa-postcode  = '69542'.
wa-city      = 'Heidelberg'.
wa-custtype  = 'P'.
wa-discount  = '003'.
wa-telephone = '06201/44889'.

MODIFY scustom FROM wa.

Deleting Lines Using DELETE

Deleting All Lines

Example

Delete all flight bookings (in the current client).

DELETE FROM SBOOK.

Specifying a WHERE Condition

Example

Delete all bookings for Lufthansa flight 0400 on 28/02/1995 (in the current client):

DELETE FROM SBOOK WHERE CARRID = 'LH'       AND
                        CONNID = '0400'     AND
                        FLDATE = '19950228'.

Specifying a Work Area

Example

Delete the booking with booking number 3 for Lufthansa flight 0400 on 28/02/2001 (in the current client).

DATA wa TYPE sbook.
wa-carrid = 'LH'.
wa-connid = '0400'.
wa-fldate = '20010228'.
wa-bookid = '00000003'.

DELETE sbook FROM wa.

Dynamic Source Code

Source Code in a Text Field

Example

Delete all lines in a database table.

PARAMETERS tabname(80) TYPE c.

DELETE FROM (tabname).

Source Code in a String

Example

Display flight connections after entering departure and destination cities.

PARAMETERS: p_from TYPE SPFLI-CITYFROM, p_to TYPE SPFLI-CITYTO.

DATA: where_clause TYPE STRING,
      carr         TYPE spfli-carrid,
      conn         TYPE spfli-connid.

CONCATENATE 'CITYFROM = ''' p_from ''' AND CITYTO = ''' p_to ''''
  INTO where_clause.

SELECT carrid connid FROM spfli
  INTO (carr, conn)
  WHERE (where_clause).
  WRITE: / carr, conn.
ENDSELECT.

Source Code in an Internal Table with Text Fields

Example

Display the number of flight bookings after entering the airline, flight number, and flight date.

PARAMETERS: p_carrid TYPE sbook-carrid,
            p_connid TYPE sbook-connid,
            p_fldate TYPE sbook-fldate.

TYPES:      t_src(80) TYPE C.

DATA: where_tab TYPE TABLE OF t_src, line TYPE t_src.

CONCATENATE 'CARRID = ''' p_carrid '''' INTO line.
APPEND line TO where_tab.
APPEND 'AND' TO where_tab.
CONCATENATE 'CONNID = ''' p_connid '''' INTO line.
APPEND line TO where_tab.
APPEND 'AND' TO where_tab.
CONCATENATE 'FLDATE = ''' p_fldate '''' INTO line.
APPEND line TO where_tab.

SELECT count(*) FROM sbook WHERE (where_tab).

WRITE: / sy-dbcnt.

Source Code in an Internal Table with Strings

Example

Display flight data for all flights with available seats (after entering airline and flight number).

PARAMETERS: p_carrid TYPE sflight-carrid,
            p_connid TYPE sflight-connid.

DATA: s         TYPE STRING,
      stringtab TYPE TABLE OF STRING,
      date      TYPE sflight-fldate.

CONCATENATE ' CARRID = ''' p_carrid ''' AND' INTO s.
APPEND s TO stringtab.
CONCATENATE ' CONNID = ''' p_connid '''' INTO s.
APPEND s TO stringtab.

SELECT fldate FROM sflight INTO date
  WHERE (stringtab) AND seatsocc < sflight~seatsmax.
  WRITE : / date.
ENDSELECT.

Example

Display a list of all airlines (with IDs and names):

DATA:  carrid   TYPE scarr-carrid,
       carrname TYPE scarr-carrname.

SELECT carrid carrname
       INTO (carrid, carrname)
       FROM scarr.
  WRITE: / carrid, carrname.
ENDSELECT.

Dynamic Logical Condition

Example

Display flight connections after entering airline and flight number.

PARAMETERS: carr_id TYPE spfli-carrid,
            conn_id TYPE spfli-connid.

DATA:       where_clause TYPE  STRING,
            and(4),
            wa_spfli TYPE spfli.

IF carr_id IS NOT INITIAL.
  CONCATENATE 'CARRID = ''' carr_id '''' INTO where_clause.
  and = ' AND'.
ENDIF.
IF conn_id IS NOT INITIAL.
  CONCATENATE where_clause and ' CONNID = ''' conn_id ''''
    INTO where_clause.
ENDIF.

SELECT * FROM spfli INTO wa_spfli WHERE (where_clause).
  WRITE: / wa_spfli-carrid, wa_spfli-connid, wa_spfli-cityfrom,
           wa_spfli-cityto, wa_spfli-deptime.
ENDSELECT.