Select Statements types
Select Single and Select up to 1 row What is the difference between Select Single and Select up to 1 rows in SAP ABAP ? These statements are the part of reading data from database table. By using SELECT SINGLE and SELECT UPTO 1 ROWS we can able to read single record from a database table . SELECT SINGLE
SELECT UP TO 1 ROWS
Used to read exact record from database table.
Used to read appropriate record from database table.
To read exact record from database table we need to provide all key fields.
We can read appropriate record from database table, we need to provide all key fields.
This statement should be used only if all the key fields are available.
This statement should be used only if we have some key no key fields.
Syntax :SELECT SINGLE * FROM DATABASETABLE INTO WA WHERE ALL KEY FIELDS
Syntax: SELECT * FROM DATABASETABLE INTO WA UP TO WHERE ALL KEY FIELDS/SOME FIELDS. ENDSELECT.
This is very fast when compared to SELECT UPTO
This is slow.
Example SELECT SINGLE DATA : WA_MARA TYPE MARA. " Declare work area
SELECT SINGLE * FROM MARA INTO WA_MARA WHERE MATNR = '0001'. " Read exact record from MARA table
write : wa_mara-matnr, wa_mara-mtart, wa_mara-meins. " Print data to screen
Example SELECT UPTO
DATA : WA_MARA TYPE MARA. " Declare work area
SELECT * FROM MARA INTO WA_MARA UP TO 1 ROWS WHERE MTART = 'FERT'. " Read appropriate record from MARA table here MTART is not a keyfield
ENDSELECT.
We can read multiple records using SELECT UPTO DATA : IT_MARA TYPE TABLE OF MARA. " Declare internal table DATA : WA_MARA TYPE MARA. " Declare work area
SELECT * FROM MARA INTO WA_MARA UP TO 50 ROWS WHERE MTART = 'FERT'. " Read 50 appropriate records from MARA table here MTART is not a keyfield ENDSELECT. LOOP AT IT_MARA INTO WA_MARA. write :/ wa_mara-matnr, wa_mara-mtart, wa_mara-meins. " Print data to screen ENDLOOP.
Select with Joins in SAP ABAP Select with Joins in SAP ABAP, What are the disadvantages of select with joins in SAP ABAP Programming ? SELECT WITH JOINS statement is used to read data simultaneously from multiple database tables . As per performance standards, SELECT WITH JOINS for more than 3 tables is not advisable, as it puts heavy load on database Syntax :
SELECT T1~FIELD1 T1~FIELD2 T2~FIELD1 T2~FIELD2 INTO TABLE FROM T1 INNER JOIN T2 ON ( T1~FIELD1 = T2~FIELD ) WHERE T1~FIELD = .
** Here T1 and T2 are database tables, FIELD1 and FIELD2 are fields in respective tables
Example of using SELECT JOINS in SAP ABAP **DATA DECLERATIONS TYPES: BEGIN OF T_MARA, MATNR LIKE MARA-MATNR,
"FIELD1 FROM MARA TABLE
MTART TYPE MARA-MTART,
"FIELD2 FROM MARA TABLE
MAKTX TYPE MAKT-MAKTX,
"FIELD1 FROM MAKT TABLE
SPRAS TYPE MAKT-SPRAS,
"FIELD2 FROM MAKT TABLE
END OF T_MARA.
DATA: IT_MARA TYPE
TABLE OF T_MARA .
DATA : WA_MARA TYPE T_MARA. SELECT MARA~MATNR MARA~MTART
MAKT~MAKTX MAKT~SPRAS INTO
TABLE IT_MARA
FROM MARA INNER JOIN MAKT ON ( MARA~MATNR = MAKT~MATNR ) UP TO 50 ROWS.
LOOP AT IT_MARA INTO WA_MARA. WRITE : / WA_MARA-MATNR, WA_MARA-MTART, WA_MARA-MAKTX, WA_MARA-SPRAS . ENDLOOP.
Select for all entries in SAP ABAP What are the advantages of select for all entries in SAP ABAP programming SELECT FOR ALL ENTRIES is the best alternative for SELECT WITH JOINS, this statement is very helpful for reading data from more than 2 tables. The load on database will be very less. Syntax :
SELECT FROM INTO TABLE WHERE . SELECT FROM INTO FOR ALL ENTRIES IN WHERE = -FIELD1. **HERE WE ARE READING DATA 2 DATABASE TABLES, SEE WHERE CONDITIONS OF SECOND SELECT STATEMENT
Ensure before using SELECT FOR ALL ENTRIES
Parent internal table must not be empty ( If it is empty, where condition fails and it will get all records from database). Remove all duplicate entries in parent internal table.
Here is the example of using SELECT FOR ALL ENTRIES in real-time applications **DATA DECLERATIONS DATA : IT_MARA TYPE TABLE OF MARA. DATA : IT_MAKT TYPE TABLE OF MAKT . **GET DATA FROM MARA TABLE SELECT * FROM MARA INTO TABLE IT_MARA WHERE MATNR = '0001'. **SORT MARA TABLE, TO DELETE ADJACENT DUPLICATES THE TABLE MUST BE SORTED IN ASCEN DING ORDER SORT IT_MARA ASCENDING . **DELETE ADJACENT DUPLICATES FROM IT_MARA COMPARING ALL FIELDS
DELETE ADJACENT DUPLICATES FROM IT_MARA COMPARING ALL FIELDS.
**TO USE SELECT FOR ALL ENTRIES, THE PARENT INTERNAL TABLE MUST NOT BE EMPTY SO CH ECK IT IF IT_MARA IS NOT INITIAL. "CHECK PARENT INTERNAL TABLE **SECOND SELECT STATEMENT TO GET DATA FROM MAKT (MATERIAL DESCRIPTIONS), HERE WE A RE GETTING DATA **FROM MAKT FOR ALL THE RECORDS IN IT_MARA, SEE WHERE CONDITION SHOULD BE PARENT I NTERNAL TABLE SELECT * FROM MAKT INTO TABLE IT_MAKT FOR ALL ENTRIES IN IT_MARA WHERE MATNR = IT_MARA-MATNR. ENDIF.
Select into corresponding fields Using select into corresponding in sap abap, disadvantages of select into corresponding statement in SAP ABAP Select into corresponding is used to get data from a data base table into a user defined internal table or work area with out specifying the list of fields. Syntax: SELECT * FROM INTO CORRESPONDING FIELDS OF TABLE .
As per SAP standards select into corresponding statement is not advisable as it effects the performance of an application because it has to compare each field with database.
Example using select into corresponding The below example explains how to use select into corresponding statement to get data into a database table with out specifying list of fields in select statement. REPORT ZSAPN_SELECT_CORRESPONDING. TYPES : BEGIN OF TY_MARA, "USER DEFINED TYPE WITH FOUR FIELDS MATNR TYPE MARA-MATNR, MTART TYPE MARA-MTART, MBRSH TYPE MARA-MBRSH, MEINS TYPE MARA-MEINS, END OF TY_MARA. DATA : IT_MARA TYPE TABLE OF TY_MARA . "INTERNAL TABLE FOR USER DEFINED TYPE
DATA : WA_MARA TYPE TY_MARA . "WORK AREA FOR USER DEFINED TYPE
SELECT * FROM MARA INTO CORRESPONDING FIELDS OF TABLE IT_MARA UP TO 50 ROWS.
LOOP AT IT_MARA INTO WA_MARA. WRITE :/ WA_MARA-MATNR, WA_MARA-MTART, WA_MARA-MBRSH, WA_MARA-MEINS. PUT
ENDLOOP.
"DISPLAY OUT
