Select Single and Select up to
Select single statement only selects the first record of
any series of records from a database table. That means this statement can read
a single record from a database table. It keeps the data into a work area or header line. This work area is generally a line
type of internal table.
In below example we are fetching records of PO no, item
no, material, plant & storage location from table EKPO where the PO no is 3000000232.
The database contains only 3 items for this PO.
REPORT zabap_gui.
TABLES: ekpo.
* Creating a custom structure of Item Table
TYPES:
BEGIN OF ty_ekpo,
ebeln TYPE ekpo-ebeln,
ebelp TYPE ekpo-ebelp,
matnr TYPE ekpo-matnr,
werks TYPE ekpo-werks,
lgort TYPE ekpo-lgort,
END OF ty_ekpo.
* Creating a line type of predefined structure
DATA:
wa_ekpo TYPE ty_ekpo.
* Select single will fetch only the First record
* from the PO Item table
SELECT SINGLE ebeln ebelp matnr werks lgort
FROM ekpo INTO wa_ekpo
WHERE ebeln = '3000000232'.
WRITE:/ 'PO No.',
15 'Item No',
28 'Material',
48 'Plant',
55 'Storage'.
ULINE.
SKIP.
WRITE:/ wa_ekpo-ebeln,
15 wa_ekpo-ebelp,
28 wa_ekpo-matnr,
48 wa_ekpo-werks,
55 wa_ekpo-lgort.
TABLES: ekpo.
* Creating a custom structure of Item Table
TYPES:
BEGIN OF ty_ekpo,
ebeln TYPE ekpo-ebeln,
ebelp TYPE ekpo-ebelp,
matnr TYPE ekpo-matnr,
werks TYPE ekpo-werks,
lgort TYPE ekpo-lgort,
END OF ty_ekpo.
* Creating a line type of predefined structure
DATA:
wa_ekpo TYPE ty_ekpo.
* Select single will fetch only the First record
* from the PO Item table
SELECT SINGLE ebeln ebelp matnr werks lgort
FROM ekpo INTO wa_ekpo
WHERE ebeln = '3000000232'.
WRITE:/ 'PO No.',
15 'Item No',
28 'Material',
48 'Plant',
55 'Storage'.
ULINE.
SKIP.
WRITE:/ wa_ekpo-ebeln,
15 wa_ekpo-ebelp,
28 wa_ekpo-matnr,
48 wa_ekpo-werks,
55 wa_ekpo-lgort.
Output of this:
The database contains 3 records here. But select single
statement fetches only single record from database. Here the first record is
always fetched by this statement.
Select up to statement is used to mention the rows need
to be selected from the database table. If the database contains that number of
rows then it will display accordingly. Here we are declaring an internal
table to store the multiple row records and print the output as well.
REPORT zabap_gui.
TABLES: ekpo.
* Creating a custom structure of Item Table
TYPES:
BEGIN OF ty_ekpo,
ebeln TYPE ekpo-ebeln,
ebelp TYPE ekpo-ebelp,
matnr TYPE ekpo-matnr,
werks TYPE ekpo-werks,
lgort TYPE ekpo-lgort,
END OF ty_ekpo.
* Creating a line type of predefined structure
DATA:
wa_ekpo TYPE ty_ekpo,
it_ekpo TYPE STANDARD TABLE OF ty_ekpo.
* Select up to n rows fetches n rows
* from the PO Item table
SELECT ebeln ebelp matnr werks lgort
UP TO 5 ROWS
FROM ekpo INTO TABLE it_ekpo
WHERE ebeln = '3000000232'.
WRITE:/ 'PO No.',
15 'Item No',
28 'Material',
48 'Plant',
55 'Storage'.
ULINE.
SKIP.
LOOP AT it_ekpo INTO wa_ekpo.
WRITE:/ wa_ekpo-ebeln,
15 wa_ekpo-ebelp,
28 wa_ekpo-matnr,
48 wa_ekpo-werks,
55 wa_ekpo-lgort.
ENDLOOP.
TABLES: ekpo.
* Creating a custom structure of Item Table
TYPES:
BEGIN OF ty_ekpo,
ebeln TYPE ekpo-ebeln,
ebelp TYPE ekpo-ebelp,
matnr TYPE ekpo-matnr,
werks TYPE ekpo-werks,
lgort TYPE ekpo-lgort,
END OF ty_ekpo.
* Creating a line type of predefined structure
DATA:
wa_ekpo TYPE ty_ekpo,
it_ekpo TYPE STANDARD TABLE OF ty_ekpo.
* Select up to n rows fetches n rows
* from the PO Item table
SELECT ebeln ebelp matnr werks lgort
UP TO 5 ROWS
FROM ekpo INTO TABLE it_ekpo
WHERE ebeln = '3000000232'.
WRITE:/ 'PO No.',
15 'Item No',
28 'Material',
48 'Plant',
55 'Storage'.
ULINE.
SKIP.
LOOP AT it_ekpo INTO wa_ekpo.
WRITE:/ wa_ekpo-ebeln,
15 wa_ekpo-ebelp,
28 wa_ekpo-matnr,
48 wa_ekpo-werks,
55 wa_ekpo-lgort.
ENDLOOP.
Output of this:
Since there are only three records in database, the system
shows only 3 records in spite of being mentioned “UP TO 5 ROWS”.