Building an SAP Query using ABAP Code

By Jayanta Bej, Capgemini

There are 4 different ways to write SAP queries – depending on the approach of data retrieval strategy:

1. Using table join
2. Directly reading from tables
3. Using logical databases (LDB) e.g. PNP
4. Using some programs

Here in this document, we’ll demonstrate building SAP query using 2^nd method (Direct read of table). 

1. Create InfoSet by using t-code SQ02:

2. Press ‘Create’ button.

Press enter.

Press enter. 

3. Press ‘Code’.

3. Select the ‘DATA’ coding section.

4. Write the following code in this section.

   TYPE-POOLS: slis.

   TABLES: scarr.

* Type for taking data from tables SFLIGHT and SCARR
   TYPES: BEGIN OF ty_flight,
            carrid   TYPE s_carr_id,
            connid   TYPE s_conn_id,
            fldate   TYPE s_date,
            seatsmax TYPE s_seatsmax,
            seatsocc TYPE s_seatsocc,
            carrname TYPE s_carrname,
            currcode TYPE s_currcode,
         END OF ty_flight.

* Internal table and work area declaration
   DATA: wa_flight TYPE ty_flight,
         i_flight  TYPE STANDARD TABLE OF ty_flight INITIAL SIZE 0,
         i_fldcat  TYPE slis_t_fieldcat_alv,
         wa_fldcat TYPE slis_fieldcat_alv.

5. Now go to START-OF-SELECTION section

And write the following code in START-OF-SELECTION section to display a few field data in ALV grid display report format. 

* Retrieve data from tables SFLIGHT and SCARR
   SELECT m1~carrid
          m1~connid
          m1~fldate
          m1~seatsmax
          m1~seatsocc
          m2~carrname
          m2~currcode
  INTO TABLE i_flight
  FROM sflight AS m1 INNER JOIN scarr AS m2
  ON m1~carrid = m2~carrid.

  CLEAR: wa_flight.

* Populate the field catalogs
  wa_fldcat-col_pos = 1.
  wa_fldcat-fieldname = 'CARRID'.
  wa_fldcat-seltext_l = 'Airline carrier ID'.
  APPEND wa_fldcat TO i_fldcat.
  CLEAR: wa_fldcat.

wa_fldcat-col_pos = 2.
wa_fldcat-fieldname = 'CONNID'.
wa_fldcat-seltext_l = 'Flight connection Id'.
APPEND wa_fldcat TO i_fldcat.
CLEAR: wa_fldcat.

wa_fldcat-col_pos = 3.
wa_fldcat-fieldname = 'FLDATE'.
wa_fldcat-seltext_l = 'Flight date'.
APPEND wa_fldcat TO i_fldcat.
CLEAR: wa_fldcat.

wa_fldcat-col_pos = 4.
wa_fldcat-fieldname = 'SEATSMAX'.
wa_fldcat-seltext_l = 'Maximum capacity'.
APPEND wa_fldcat TO i_fldcat.
CLEAR: wa_fldcat.

wa_fldcat-col_pos = 5.
wa_fldcat-fieldname = 'SEATSOCC'.
wa_fldcat-seltext_l = 'Occupied seats'.
APPEND wa_fldcat TO i_fldcat.
CLEAR: wa_fldcat.

wa_fldcat-col_pos = 6.
wa_fldcat-fieldname = 'CARRNAME'.
wa_fldcat-seltext_l = 'Airline name'.
APPEND wa_fldcat TO i_fldcat.
CLEAR: wa_fldcat.

wa_fldcat-col_pos = 7.
wa_fldcat-fieldname = 'CURRCODE'.
wa_fldcat-seltext_l = 'Local currency of airline'.
APPEND wa_fldcat TO i_fldcat.
CLEAR: wa_fldcat.

* ALV grid display report
   CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
     EXPORTING
          it_fieldcat   = i_fldcat
     TABLES
          t_outtab      = i_flight
     EXCEPTIONS
          program_error = 1
          OTHERS        = 2.

IF sy-subrc <> 0.
  MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

EXIT.

Click here to continue...