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”.
Post a Comment