Ansicht
Dokumentation
ABENOPEN_SQL_EXAMPLES - OPEN SQL EXAMPLES
BAL_S_LOG - Application Log: Log header data PERFORM Short ReferenceThis documentation is copyright by SAP AG.
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.
Fill RESBD Structure from EBP Component Structure BAL_S_LOG - Application Log: Log header data
This documentation is copyright by SAP AG.
Length: 72700 Date: 20240425 Time: 083856 sap01-206 ( 284 ms )
