Quick Contact

    SAP Tutorial

    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.
    

    Copyright 1999- Ducat Creative, All rights reserved.