Create Open SQL query dynamically out of custom table

Open SQL offers really flexible way of use. In below example you can see dynamically created query, there is not clear from the beginning what we are looking for, from which table and under what WHERE condition. The example shows how to get data for a certain user based on another custom table storing target table name and expected field name for username. That's all. The rest is a dynamic miracle :)

Fully flexible query:

    SELECT SINGLE (<fs_user_table>-fieldname) FROM (<fs_user_table>-tabname)
    INTO gv_text
    WHERE (gt_sql_where).


Note: Even target INTO variable can be created dynamically - see another post.

Source table for Query

Structure of custom table Z_USER_TABS:

Possible table content (user related tables):

ABAP coding:

REPORT z_osql_dynamics.

PARAMETER p_user TYPE xubname.

" Get/List user param table list
TYPES: BEGIN OF tys_user_tab,
        tabname   TYPE tabname,
        fieldname TYPE fieldname,
       END OF tys_user_tab.

TYPES tyv_user_tab TYPE c LENGTH 72.

DATA: gt_user_tables TYPE STANDARD TABLE OF tys_user_tab,
      gt_sql_where   TYPE STANDARD TABLE OF tyv_user_tab,
      gv_sql_where   LIKE LINE OF gt_sql_where,
      gv_text        TYPE string.


START-OF-SELECTION.

  " Get list of all user param tables to be checked
  SELECT tabname fieldname
  FROM z_user_tabs
  INTO TABLE gt_user_tables.

  FIELD-SYMBOLS <fs_user_table> LIKE LINE OF gt_user_tables.
  LOOP AT gt_user_tables ASSIGNING <fs_user_table>.

    CLEAR gv_sql_where.
    CLEAR gt_sql_where.

    CONCATENATE <fs_user_table>-fieldname ' = ' '`' p_user '`' INTO gv_sql_where RESPECTING BLANKS.
    APPEND gv_sql_where TO gt_sql_where.

    SELECT SINGLE (<fs_user_table>-fieldname) FROM (<fs_user_table>-tabname)
    INTO gv_text
    WHERE (gt_sql_where).
    IF sy-subrc = 0.
      WRITE: / <fs_user_table>-tabname, <fs_user_table>-fieldname, ' = ', gv_text.
    ENDIF.
  ENDLOOP.

END-OF-SELECTION.

Display table content in a popup window via SALV

If you have a list of various db tables with different structure (selected by specific field name) you may want add a preview of each ones content via popup window. But field name could be a variable of unknown type. Below coding shows a way how to reach this functionality, it uses dynamic data creation, dynamic Open SQL and SALV class.

Note: Sorry for the ugly subroutine surrounding ;-) I placed this functionality into an old program, where is the subroutine triggered by event  AT LINE-SELECTION working  together with variables provided by HIDE command. The popup window nicely works with selection screen.



TYPES tyv_sql_line TYPE c LENGTH 72.

*&---------------------------------------------------------------------*
*&      Form  show_table_content
*&---------------------------------------------------------------------*
*       Show table content in a popup window by specific field
*----------------------------------------------------------------------*
FORM show_table_content USING p_tablename p_fieldname p_fieldvalue.

  DATA: lt_sql_where   TYPE STANDARD TABLE OF tyv_sql_line,
              lv_sql_where   LIKE LINE OF lt_sql_where,
              rv_dref        TYPE REF TO data,
              rcl_alv        TYPE REF TO cl_salv_table,
              lx_msg         TYPE REF TO cx_salv_msg.

  FIELD-SYMBOLS: <itab> TYPE ANY TABLE.

  CLEAR lv_sql_where.
  CLEAR lt_sql_where.

  CONCATENATE p_fieldname ' = ' '`' p_fieldvalue '`' INTO lv_sql_where RESPECTING BLANKS.
  APPEND lv_sql_where TO lt_sql_where.

  CREATE DATA rv_dref TYPE TABLE OF (p_tablename).
  ASSIGN rv_dref->* TO <itab>.

  SELECT * FROM (p_tablename)
  INTO TABLE <itab>
  WHERE (lt_sql_where).

  IF <itab> IS ASSIGNED.

    TRY.
        cl_salv_table=>factory(
        IMPORTING
          r_salv_table = rcl_alv
        CHANGING
          t_table      = <itab> ).
      CATCH cx_salv_msg INTO lx_msg.
    ENDTRY.

    rcl_alv->set_screen_popup(
      start_column = 25
      end_column   = 150
      start_line   = 15
      end_line     = 22
    ).

    rcl_alv->display( ).

  ENDIF.

ENDFORM.                    "show_table_content



The popup looks like e.g.

Valuable source for ALV popup