Select Statements

Select Statements

Select single and Select up to 1 rows

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 may not 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 fields or no key fields.
Syntax: SELECT SINGLE * FROM DATABASETABLE INTO WA WHERE ALL KEY FIELDS	Syntax: SELECT * FROM DATABASETABLE INTO WA UP TO 1 ROWS 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 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 a heavy load on the database

Syntax:

SELECTT1~FIELD1
       T1~FIELD2
       T2~FIELD1
        T2~FIELD2
       INTOTABLE
FROMT1INNERJOINT2ON(T1~FIELD1=T2~FIELD)
WHERET1~FIELD=.

** Here T1 and T2 are database tables, FIELD1 and FIELD2 are fields in the 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

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 < FIELDS> FROM < DBTABLE1> INTO TABLE < ITAB1>
WHERE < CONDITION>.
SELECT < FIELDS> FROM < DBTABLE2> INTO < ITAB2> FOR ALL ENTRIES IN < ITAB1>
WHERE < FIELD1> = < ITAB1>-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: it_mara TYPE TABLE OF mara,
wa_mara TYPE mara.
DATA: it_makt TYPE TABLE OF makt,
wa_makt TYPE makt.
SELECT *
  FROM mara
  INTO TABLE it_mara
  WHERE mtart = 'FERT'.
IF it_mara IS NOT INITIAL.
  SELECT *
    FROM makt
    INTO TABLE it_makt
    FOR ALL ENTRIES IN it_mara
    WHERE matnr = it_mara-matnr.
ENDIF.

Select into corresponding fields in SAP ABAP

Select into corresponding is used to get data from a database table into a user defined internal table or work area without specifying the list of fields.

Syntax: SELECT * FROM < DATABASE TABLE>

INTO CORRESPONDING FIELDS OF TABLE < INTERNAL TABLE>.

As per SAP standards select into corresponding statement is not advisable as it affects 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 without 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.  "DISPLAY OUTPUT
ENDLOOP.

SELECT DISTINCT in SAP ABAP

SELECT DISTINCT is a SQL Select query, which is used to get distinct values of a column from a database table.

SELECT DISTINCT eliminates duplicates records of a column of a table.

Syntax for SELECT DISTINCT in SAP ABAP

SELECT DISTINCT < COLUMN> FROM < TABLE> INTO TABLE < ITAB> WHERE < CONDITION>.

Example for SELECT DISTINCT in SAP ABAP

The below example is used to get distinct MTART(Material type) from MARA(Material Master) table.

  TYPES: BEGIN OF ty_mtart,
mtart TYPE mara-mtart,
         END OF ty_mtart.
  DATA: it_mtart TYPE TABLE OF ty_mtart,
wa_mtart TYPE          ty_mtart.
START-OF-SELECTION.
  SELECT DISTINCT mtart FROM mara INTO TABLE it_mtart UP TO 5 ROWS.

  LOOP AT it_mtart INTO wa_mtart.
    WRITE:/wa_mtart-mtart.
  ENDLOOP.

SELECT with BYPASSING BUFFER in SAP ABAP

Whenever we use open SQL statements to get fetch data in SAP, it will get data from buffer area(depends on table buffer settings) for better performance, but in real world scenarios some tables may updated very frequently(milliseconds), we may need to bypass buffer to get real-time data, in that case we will bypass buffer using ‘BYPASSING BUFFER’ keyword.

Syntax for SELECT WITH BYPASSING BUFFER in SAP ABAP

select * FROM < TABLE> INTO TABLE < ITAB> BYPASSING BUFFER.

Example for SELECT WITH BYPASSING BUFFER in SAP ABAP

The below example is used to fetch data from MARA(Material Master) table bypassing buffer.

TYPES: BEGIN OF ty_mara,
matnr TYPE mara-matnr,
mtart TYPE mara-mtart,
       END OF ty_mara.
DATA: it_mara TYPE TABLE OF ty_mara,
wa_mara TYPE          ty_mara.

START-OF-SELECTION.
  SELECT matnrmtart FROM mara INTO TABLE it_mara BYPASSING BUFFER.

  LOOP AT it_mara INTO wa_mara.
    WRITE:/wa_mara-matnr, wa_mara-mtart.
  ENDLOOP.

SELECT APPENDING

• SELECT APPENDING query is used to append SELECT query result directly to some other internal table.
• Below is the syntax for SELECT APPENDING

SELECT < FIELD1> < FIELD2>
 from < DATABASE_TABLE> 
 APPENDING TABLE < ITAB>
 WHERE < FIELD> = < COND_VALUE>.

Note: Where condition is optional in SELECT Query

Below is the complete example program, in the below program, we will use select appending to append records to same internal table.

TYPES: BEGIN OF ty_mara,
matnr TYPE mara-matnr,
mtart TYPE mara-mtart,
       END OF ty_mara.
DATA: it_mara TYPE TABLE OF ty_mara.
DATA: wa_mara TYPE ty_mara.
**Get first 10 records from MARA where MTART = FERT
SELECT matnrmtart
  FROM mara
  INTO TABLE it_mara
  UP TO 10 ROWS
  WHERE mtart = 'FERT'
  .

**Get 10 reords from MARA where MTART = HALB append them to same internal table

SELECT matnrmtart
  FROM mara
  APPENDING TABLE it_mara
  UP TO 10 ROWS
  WHERE mtart = 'HALB'.

LOOP AT it_mara INTO wa_mara.
  WRITE:/wa_mara-matnr, wa_mara-mtart.
ENDLOOP.