SAP Open SQL

PARAMETERS p_carrid TYPE sflight-carrid.

CLASS demo DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS main.
  PRIVATE SECTION.
    CLASS-DATA: BEGIN OF result_line,
                  carrid TYPE sflight-carrid,
                  connid TYPE sflight-connid,
                  fldate TYPE sflight-fldate,
                END OF result_line,
                result_tab LIKE TABLE OF result_line.
    CLASS-METHODS display.
ENDCLASS.

CLASS demo IMPLEMENTATION.
  METHOD main.
    DATA:  sql     TYPE REF TO cl_sql_statement,
           result  TYPE REF TO cl_sql_result_set,
           err     TYPE REF TO cx_sql_exception,
           cols    TYPE adbc_column_tab,
           dref    TYPE REF TO data.
    APPEND 'CARRID' TO cols.
    APPEND 'CONNID' TO cols.
    APPEND 'FLDATE' TO cols.
    CREATE OBJECT sql.
    GET REFERENCE OF result_tab INTO dref.
    TRY.
        result = sql->execute_query(
         `SELECT carrid, connid, fldate ` &&
         `FROM sflight ` &&
         `WHERE mandt  = ` && `'` && sy-mandt && `' AND` &&
         `      carrid = ` && `'` && p_carrid && `'` ).
        result->set_param_table( itab_ref = dref
                                 corresponding_fields = cols ).
        IF result->next_package( ) > 0.
          SORT result_tab BY carrid connid fldate.
          display( ).
        ENDIF.
      CATCH cx_sql_exception INTO err.
        MESSAGE err TYPE 'I' DISPLAY LIKE 'E'.
    ENDTRY.
  ENDMETHOD.
  METHOD display.
    DATA alv TYPE REF TO cl_salv_table.
    DATA err TYPE REF TO cx_salv_msg.
    TRY.
        cl_salv_table=>factory(
          IMPORTING r_salv_table = alv
          CHANGING  t_table      = result_tab ).
        alv->display( ).
      CATCH cx_salv_msg INTO err.
        MESSAGE err TYPE 'I' DISPLAY LIKE 'E'.
    ENDTRY.
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  demo=>main( ).

 

 

report  ztest158.

data: ok_code like sy-ucomm.
data: g_text type ref to cl_gui_textedit ,
  g_custom_container type ref to cl_gui_custom_container.
data: it_result type table of string ,
      wa_select type string,
      wa_string type string,
      lv_sql type string,
      lv_string type string.
data: it_marc like standard table of marc .
data: g_tabname type slis_tabname,
      gt_fieldcat type lvc_t_fcat,
      wa_fieldcat type lvc_s_fcat,
     gt_fieldcat2 type slis_t_fieldcat_alv,
      wa_fieldcat2 type slis_fieldcat_alv,
      itab type ref to data,
      g_repid like sy-repid.
field-symbols: <itab> type standard table.
data:  sql     type ref to cl_sql_statement,
       result  type ref to cl_sql_result_set,
       err     type ref to cx_sql_exception,
       cols    type adbc_column_tab,
       dref    type ref to data.
define create_stru.
  wa_fieldcat-fieldname = &1.
  wa_fieldcat-tabname = &2.
  wa_fieldcat-ref_field = &3.
  wa_fieldcat-ref_table = &4.
  append wa_fieldcat to gt_fieldcat.
end-of-definition.

start-of-selection.
  call screen '0100'.
*&---------------------------------------------------------------------*
*&      Module  STATUS_0100  OUTPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
module status_0100 output.
  set pf-status '0100'.
*  SET TITLEBAR 'xxx'.

endmodule.                 " STATUS_0100  OUTPUT
*&---------------------------------------------------------------------*
*&      Module  EXIT_SCREEN  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
module exit_screen input.
  leave to screen 0.
endmodule.                 " EXIT_SCREEN  INPUT

*&---------------------------------------------------------------------*
*&      Form  DISPLAY_TXT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
module display_txt output.
  class cl_gui_cfw definition load.
  if g_custom_container is initial.

    create object g_custom_container
      exporting
        container_name              = 'TXTEDIT'
      exceptions
        cntl_error                  = 1
        cntl_system_error           = 2
        create_error                = 3
        lifetime_error              = 4
        lifetime_dynpro_dynpro_link = 5
        others                      = 6.
    if sy-subrc ne 0.
      message id sy-msgid type sy-msgty number sy-msgno
      with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    endif.

    create object g_text
      exporting
        parent = g_custom_container.

    if sy-subrc ne 0.
      message id sy-msgid type sy-msgty number sy-msgno
      with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    endif.


    call method cl_gui_cfw=>flush
      exceptions
        cntl_system_error = 1
        cntl_error        = 2.
  endif.
endmodule.                    " DISPLAY_TXT
*&---------------------------------------------------------------------*
*&      Module  USER_COMMAND_0100  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
module user_command_0100 input.
  if ok_code = 'EX'.
    perform get_string.
  endif.
endmodule.                 " USER_COMMAND_0100  INPUT
*&---------------------------------------------------------------------*
*&      Form  GET_STRING
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form get_string .
  clear: lv_string,wa_string,wa_select,it_marc[],gt_fieldcat[],cols[].
  call method g_text->get_textstream
    importing
      text                   = wa_string
    exceptions
      error_cntl_call_method = 1
      not_supported_by_gui   = 2
      others                 = 3.

  call method g_text->get_selected_textstream
    importing
      selected_text          = wa_select
    exceptions
      error_cntl_call_method = 1
      not_supported_by_gui   = 2
      others                 = 3.

  call method cl_gui_cfw=>flush
    exceptions
      cntl_system_error = 1
      cntl_error        = 2.
  if  wa_select is not initial.
    lv_string = wa_select.
  else.
    lv_string = wa_string.
  endif.
  check lv_string is not initial.
  split lv_string at cl_abap_char_utilities=>cr_lf into table it_result.
  clear:lv_string,wa_string.
  loop at it_result into wa_string.
    condense wa_string.
    translate wa_string to upper case.
    if lv_string is  initial.
      lv_string = wa_string.
    else.
      concatenate  lv_string  wa_string into  lv_string separated by space.
    endif .
  endloop.
  lv_sql = lv_string.
  search lv_sql for 'WHERE'.
  if sy-subrc eq 0.
    concatenate lv_sql ' AND MANDT =' sy-mandt into lv_sql.
  endif.
  perform shift_char using 'SELECT' changing lv_string.
  perform shift_char using 'FROM' changing lv_string.
  if lv_string = '*'.
    perform selct_sql.
  else.
    perform selct_sql2.
  endif.

  clear:it_result.
  split lv_string at ',' into table it_result.

endform.                    " GET_STRING
*&---------------------------------------------------------------------*
*&      Form  SHIFT_CHAR
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_0278   text
*      <--P_LV_STRING  text
*----------------------------------------------------------------------*
form shift_char  using    p_char
                 changing p_string.
  data: it_str type table of string .
  condense p_string.
  split p_string at p_char into table it_str.
  clear: p_string.
  loop at it_str into wa_string.
    if p_char = 'SELECT' and sy-tabix = 2.
      p_string = wa_string.
    endif.
    if p_char = 'FROM' and sy-tabix = 1.
      p_string = wa_string.
    endif.
  endloop.
  condense p_string.
endform.                    " SHIFT_CHAR
*&---------------------------------------------------------------------*
*&      Form  SELCT_SQL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form selct_sql .

  call function 'REUSE_ALV_FIELDCATALOG_MERGE'
    exporting
      i_program_name     = g_repid
      i_internal_tabname = g_tabname
      i_structure_name   = 'MARC'
    changing
      ct_fieldcat        = gt_fieldcat2[].

  loop at gt_fieldcat2 into wa_fieldcat2.
    append wa_fieldcat2-fieldname to cols.
  endloop.
  create object sql.
  get reference of it_marc into dref.
  try.
      result = sql->execute_query(  lv_sql ).
      result->set_param_table( itab_ref = dref
                               corresponding_fields = cols ).
      if result->next_package( ) > 0.

        perform display.
      endif.
    catch cx_sql_exception into err.
      message err type 'I' display like 'E'.
  endtry.
endform.                    " SELCT_SQL
*&---------------------------------------------------------------------*
*&      Form  DISPLAY
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form display .
  data alv type ref to cl_salv_table.
  data err type ref to cx_salv_msg.
  try.
      cl_salv_table=>factory(
        importing r_salv_table = alv
        changing  t_table      = it_marc ).
      alv->display( ).
    catch cx_salv_msg into err.
      message err type 'I' display like 'E'.
  endtry.
endform.                    " DISPLAY
*&---------------------------------------------------------------------*
*&      Form  SELCT_SQL2
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form selct_sql2 .
  create_stru 'MATNR'  'MARC' 'MATNR'  'MARC'.
  create_stru 'WERKS'  'MARC' 'WERKS'  'MARC'.

  call method cl_alv_table_create=>create_dynamic_table
    exporting
      it_fieldcatalog = gt_fieldcat
    importing
      ep_table        = itab.

  assign itab->* to <itab>.

  loop at gt_fieldcat into wa_fieldcat.
    append wa_fieldcat-fieldname to cols.
  endloop.
  create object sql.
  get reference of <itab> into dref.
  try.
      result = sql->execute_query(  lv_sql ).
      result->set_param_table( itab_ref = dref
                               corresponding_fields = cols ).
      if result->next_package( ) > 0.

        perform display1.
      endif.
    catch cx_sql_exception into err.
      message err type 'I' display like 'E'.
  endtry.
endform.                    " SELCT_SQL2
*&---------------------------------------------------------------------*
*&      Form  display1
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form display1 .
  data alv type ref to cl_salv_table.
  data err type ref to cx_salv_msg.
  try.
      cl_salv_table=>factory(
        importing r_salv_table = alv
        changing  t_table      = <itab> ).
      alv->display( ).
    catch cx_salv_msg into err.
      message err type 'I' display like 'E'.
  endtry.
endform.                    " DISPLAY