How to use Dynamic SELECT Query in SAP ABAP?

Introduction

In this post, we will write a code and understand the use of dynamic SELECT Query in ABAP. We will be using object-oriented programming in ABAP for this code. We may need to use a dynamic SELECT statement where we want flexibility with the program. The program may have varying combinations of fields, tables, and WHERE clauses during execution which can only be achieved by using a dynamic SELECT statement. Now without any further ado let's jump right into it.

Happy reading!

Table of Content

• Pre-requisites
• Problem Statement
• Solution

Pre-requisites

• Basic ABAP OOP Concept
• Basic knowledge of ABAP Syntax.
• Basic knowledge of SQL Query.

Problem Statement

Use SELECT query to fetch data from a table dynamically. List of fields, table name, and WHERE clause will be passed during runtime.

Solution

We will create a method, pass all the necessary parameters needed for the SELECT statement, and dynamically produce the output table.

Source code:

  	
REPORT ztestasr_exp05.

*--------------------------------------------------------------------*
INITIALIZATION.
*--------------------------------------------------------------------*

*Define local class LCL_MAIN.
CLASS lcl_main DEFINITION.
  PUBLIC SECTION.
    METHODS :
*Define method F_GET_DATA.
      f_get_data
        IMPORTING
          VALUE(im_tab_name)     TYPE tabname16
          VALUE(im_field_list)   TYPE name_feld_tty
          VALUE(im_where_clause) TYPE string
        EXPORTING
          VALUE(ex_table)        TYPE data.
ENDCLASS.

*Implement local class LCL_MAIN.
CLASS lcl_main IMPLEMENTATION.

*Implement method F_GET_DATA.
  METHOD f_get_data.

*Local data declaration.
    DATA : lv_field_str    TYPE string VALUE IS INITIAL,
           lv_tab_name     TYPE tabname16 VALUE IS INITIAL,
           lv_where_clause TYPE string VALUE IS INITIAL,
           lo_results      TYPE REF TO data.

    FIELD-SYMBOLS : <lfs_table> TYPE ANY TABLE.

*Assign table dynamically.
    lv_tab_name = im_tab_name.
    CREATE DATA lo_results TYPE TABLE OF (lv_tab_name).
    ASSIGN lo_results->* TO <lfs_table>.

*Convert field list table into string.
    LOOP AT im_field_list INTO DATA(ls_field).
      IF lv_field_str IS INITIAL.
        lv_field_str = ls_field.
      ELSE.
        lv_field_str = |{ lv_field_str } { ls_field }|.
      ENDIF.
    ENDLOOP.

    lv_where_clause = im_where_clause.

    IF lv_field_str IS NOT INITIAL AND
       lv_tab_name IS NOT INITIAL AND
       <lfs_table> IS ASSIGNED AND
       lv_where_clause IS NOT INITIAL.

*Execute dynamic select query.
      SELECT
      (lv_field_str)
      FROM (lv_tab_name)
      INTO CORRESPONDING FIELDS OF TABLE <lfs_table>
      WHERE (lv_where_clause).

    ENDIF.

*Return output table dynamically.
    ex_table = <lfs_table>.

  ENDMETHOD.
ENDCLASS.

*--------------------------------------------------------------------*
START-OF-SELECTION.
*--------------------------------------------------------------------*
*Global data declaration.
  DATA : go_main         TYPE REF TO lcl_main,
         go_cxroot       TYPE REF TO cx_root,
         gt_mara         TYPE TABLE OF mara,
         gv_tab_name     TYPE tabname16,
         gv_where_clause TYPE string,
         gt_flds         TYPE name_feld_tty.

*Display Selection screen.
  SELECTION-SCREEN : BEGIN OF BLOCK __b1__.
  PARAMETERS : p_matnr TYPE matnr OBLIGATORY.
  SELECTION-SCREEN : END OF BLOCK __b1__.

*Initialize reference object for class LCL_MAIN.
  CREATE OBJECT go_main.

*Populate table with Field names for
*Dynamic select query.
  gt_flds = VALUE #(
                      ( 'MATNR' )
                      ( 'ERSDA' )
                      ( 'VPSTA' )
                      ( 'PSTAT' )
                    ).

  gv_tab_name = 'MARA'.

*Prepare where clause for dynamic select.
  REPLACE ALL OCCURENCES OF '*' IN p_matnr WITH '%'.
  IF sy-subrc IS INITIAL.
    gv_where_clause = |MATNR LIKE '{ p_matnr }'|.
  ELSE.
    gv_where_clause = |MATNR EQ '{ p_matnr }'|.
  ENDIF.

  TRY .
*Calling method F_GET_DATA of class LCL_MAIN to
*fetch data using Dynamic select query.
      CALL METHOD go_main->f_get_data
        EXPORTING
          im_field_list   = gt_flds
          im_tab_name     = gv_tab_name
          im_where_clause = gv_where_clause
        IMPORTING
          ex_table        = gt_mara.
    CATCH cx_root INTO go_cxroot.
  ENDTRY.

*--------------------------------------------------------------------*
END-OF-SELECTION.
*--------------------------------------------------------------------*
*Display output.
  cl_demo_output=>display( gt_mara ).
    
  

Input Screen:

Output Screen:

Conclusion

Now we know how to make use of the SELECT statement dynamically. Please be aware that inline data declaration and OpenSQL format is not supported in dynamic SELECT Query. Also, the use of Exception Handling is highly recommended. Because the SELECT statement is finalized during runtime so any inconsistency in the statement will result in a Runtime error or Short dump. Hope this was useful to you. Please leave your feedback in the comments.

See you later!