CTE ( Common Table Expressions )

 CTE: 

ABAP has provided a fantastic way to increase the performance of applications through CTE. 

This seems be very familiar to AMDP procedures. I am calling it the AMDP flavor with OpenSQL. 

Below snippet is an example of how you can avoid multiple select queries and combine them into one. This way we can use subqueries and internal tables to bring out the most from OpenSQL. 

The ultimately the goal is to hit the database as less as possible and bring data as much as possible. 

WITH +kun AS (
          SELECT DISTINCT bseg~belnr , kna1~kunnr, kna1~name1 FROM kna1
          JOIN bseg ON bseg~kunnr = kna1~kunnr AND bseg~koart = 'D'
          JOIN bkpf ON bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
          WHERE bkpf~belnr IN @s_belnr AND 

          bkpf~bukrs = @p_bukrs AND bkpf~gjahr = @p_gjahr

          )
SELECT bkpf~belnr,
  bkpf~bldat,
  +kun~name1,
  SUM( bseg~wrbtr ) AS wrbtr,
  bkpf~waers,
  CAST( ' ' AS CHAR( 255 ) ) AS in_word,
  bseg~sgtxt,
  bkpf~xblnr as bktxt, "bkpf~bktxt,
  bseg~zuonr
  FROM bkpf JOIN bseg
  ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
  AND bseg~hkont BETWEEN '0025000000' AND '0025999999'
  LEFT JOIN +kun
  ON bseg~belnr = +kun~belnr "and kna1~kunnr <> ' '
  WHERE bkpf~bukrs = @p_bukrs AND bkpf~belnr IN @s_belnr AND bkpf~gjahr = @p_gjahr
  GROUP BY bkpf~belnr, bkpf~bldat, bkpf~waers, +kun~name1, bseg~sgtxt, bkpf~xblnr, bseg~zuonr
  INTO TABLE @DATA(it_final) .

ALV with IDA (Integrated Data Access)

 ALV With IDA 

**Check DB Capabilities
CHECK cl_salv_gui_table_ida=>db_capabilities( )->is_table_supported( iv_ddic_table_name = 'ZTable_name').

"Create IDA
DATA(o_ida) = cl_salv_gui_table_ida=>create( iv_table_name =  'ZTABLE_NAME' ).
o_ida->field_catalog( )->set_available_fields( its_field_names = VALUE if_salv_gui_types_ida=>yts_field_name(
( CONV fieldname( 'LAUFI') )
( CONV fieldname( 'LAUFD') )
( CONV fieldname( 'PRODUCT_CODE') )
( CONV fieldname( 'COUNTRY_CODE') )
( CONV fieldname( 'ACCOUNT_NUMBER') )
( CONV fieldname( 'TRANS_REF_NUM') )
( CONV fieldname( 'PAYMENT_CURR') )
( CONV fieldname( 'PAYMENT_AMOUNT') )
( CONV fieldname( 'STATUS') )
( CONV fieldname( 'REMARKS') )
( CONV fieldname( 'BENEF_NAME') )
( CONV fieldname( 'BENEF_ACCOUNT') )
( CONV fieldname( 'BENEF_BRC') )
( CONV fieldattributes( 'PAYMENT_AMOUNT') )
 ) ).


"Set Maximum Rows Recommended
IF cl_salv_gui_table_ida=>db_capabilities( )->is_max_rows_recommended( ).

o_ida->set_maximum_number_of_rows(  iv_number_of_rows = 2000 ).

ENDIF.

"Handling Select options 
DATA(lo_range_collector) = NEW cl_salv_range_tab_collector( ).

lo_range_collector->add_ranges_for_name( iv_name = 'LAUFI' it_ranges = s_laufi[] ).
lo_range_collector->add_ranges_for_name( iv_name = 'LAUFD' it_ranges = s_laufd[] ).
lo_range_collector->add_ranges_for_name( iv_name = 'TRANS_REF_NUM' it_ranges = s_ref[] ).
lo_range_collector->add_ranges_for_name( iv_name = 'STATUS' it_ranges = s_stat[] ).
lo_range_collector->get_collected_ranges(
                    IMPORTING
                     et_named_ranges = DATA(lt_select_options) ).

DATA: ls_aggr_rule  TYPE if_salv_gui_types_ida=>ys_aggregation_rule,
      lt_aggr_rules TYPE if_salv_gui_types_ida=>yt_aggregation_rule.

*ls_aggr_rule-field_name = 'PAYMENT_AMOUNT'.
*ls_aggr_rule-function   = if_salv_service_types=>cs_function_code-sum.
*APPEND ls_aggr_rule TO lt_aggr_rules.

o_ida->set_select_options( it_ranges    = lt_select_options ).
*o_ida->default_layout( )->set_aggregations( lt_aggr_rules ).
"Display
o_ida->fullscreen( )->display( ).

BDC Upload Program Snippet

 TOP Include : 

TYPE-POOLS : slis.
TYPES : BEGIN OF str_tab,
          "Our Structure/template for upload goes here."
        END OF str_tab.

DATA:   bdcdata LIKE bdcdata    OCCURS 0 WITH HEADER LINE.
DATA:   messtab LIKE bdcmsgcoll OCCURS 0 WITH HEADER LINE.
DATA : wa_messtab TYPE bdcmsgcoll,
       gv_msg     TYPE string.
DATA : it_itab TYPE TABLE OF str_tab.
DATA : it_itab1 TYPE TABLE OF str_tab.
TYPES: BEGIN OF ty_msg,
         DOC TYPE vblnr,
         type   TYPE char1,
         msg    TYPE char255,
       END OF ty_msg.

DATA: lt_msg TYPE TABLE OF ty_msg,
      ls_msg TYPE ty_msg.

DATA: nodata TYPE c VALUE '/'.

DATA: ctu    TYPE c VALUE 'X',
      mode   TYPE c VALUE 'N',
      update TYPE c VALUE 'S'.

DATA: mess     TYPE TABLE OF bdcmsgcoll,
      msg(255) TYPE c.

DATA: wa_layout TYPE lvc_s_layo.
DATA: lv_flag TYPE char1.

 Selection Screen Include :  

SELECTION-SCREEN : BEGIN OF BLOCK sel.

  PARAMETERS: filename LIKE rlgrap-filename OBLIGATORY.
  PARAMETERS: p_srow TYPE i.
  PARAMETERS: p_erow TYPE i.

SELECTION-SCREEN END OF BLOCK sel.

Forms Include :  

FORM file_f4  CHANGING p_filename.

  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      program_name  = syst-cprog
      dynpro_number = syst-dynnr
*
      field_name    = ' '
    IMPORTING
      file_name     = filename.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form tab_file_upd
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM tab_file_upd .

  DATA : it_data TYPE TABLE OF alsmex_tabline.
  DATA : wa_data TYPE alsmex_tabline.
  DATA : wa_itab TYPE str_tab.
  DATA : value TYPE cawao_s_fields-num_val.
  DATA : date1 TYPE d.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = filename
      i_begin_col             = '1'
      i_begin_row             = p_srow
      i_end_col               = '30'
      i_end_row               = p_erow
    TABLES
      intern                  = it_data
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

  LOOP AT it_data INTO wa_data.

"Get column wise data from excel. 
    CASE wa_data-col.
      WHEN '001'.
        MOVE wa_data-value TO wa_itab-bldat.
      WHEN '002'.
        MOVE wa_data-value TO wa_itab-blart.
      
    ENDCASE.

    AT END OF row.
      APPEND wa_itab TO it_itab1.
      it_itab = it_itab1.
      CLEAR wa_itab.
    ENDAT.

  ENDLOOP.


ENDFORM.
*&---------------------------------------------------------------------*
*& Form bdc_open
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM bdc_open .

  DATA: lv_index     LIKE sy-tabix,
        lv_idx       LIKE sy-tabix,
        lv_fieldname TYPE string.
  DATA: count TYPE i.
  LOOP AT it_itab1 INTO DATA(wa_itab1).
    lv_idx = sy-tabix.
    CLEAR bdcdata.
    REFRESH bdcdata.

"Here goes the BDC extract from SHDB 
perform bdc_dynpro      using 'SAPMF05A' '0110'.
perform bdc_field       using 'BDC_CURSOR'
                              'BSEG-VALUT'.

"End of BDC extract 

    CALL TRANSACTION 'Tcode_name' USING bdcdata
                                MODE mode
                                UPDATE update
                                MESSAGES INTO mess.

      LOOP AT mess ASSIGNING FIELD-SYMBOL(<fs_mess>) WHERE msgtyp = 'E' or msgtyp = 'S'.
      CALL FUNCTION 'FORMAT_MESSAGE'
        EXPORTING
          id        = <fs_mess>-msgid
          lang      = 'EN'
          no        = <fs_mess>-msgnr
          v1        = <fs_mess>-msgv1
          v2        = <fs_mess>-msgv2
          v3        = <fs_mess>-msgv3
          v4        = <fs_mess>-msgv4
        IMPORTING
          msg       = gv_msg
        EXCEPTIONS
          not_found = 1
          OTHERS    = 2.

      ls_msg-doc = lv_idx.
      ls_msg-type = <fs_mess>-msgtyp.
      ls_msg-msg = gv_msg.

      APPEND ls_msg TO lt_msg.
      CLEAR: ls_msg.
      endloop.
      clear mess.

  ENDLOOP.

ENDFORM.

FORM bdc_field USING fnam fval.
  IF fval <> nodata.
    CLEAR bdcdata.
    bdcdata-fnam = fnam.
    bdcdata-fval = fval.
    APPEND bdcdata.
  ENDIF.
ENDFORM.
FORM bdc_dynpro USING program dynpro.
  CLEAR bdcdata.
  bdcdata-program  = program.
  bdcdata-dynpro   = dynpro.
  bdcdata-dynbegin = 'X'.
  APPEND bdcdata.
ENDFORM.

*&---------------------------------------------------------------------*
*& Form display_log
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM display_log .
  wa_layout-cwidth_opt = 'X'.
  wa_layout-zebra = 'X'.

  DATA(lt_fldcat) = VALUE lvc_t_fcat( 

        ( fieldname = 'DOC' coltext = 'ID' tabname = 'LT_MSG' )
        ( fieldname = 'TYPE' coltext = 'Type' tabname = 'LT_MSG' )
        ( fieldname = 'MSG' coltext = 'Message' tabname = 'LT_MSG' ) ).


  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
    EXPORTING
      i_callback_program = sy-repid
*     I_CALLBACK_TOP_OF_PAGE   = ' '
*     I_CALLBACK_HTML_TOP_OF_PAGE       = ' '
*     I_CALLBACK_HTML_END_OF_LIST       = ' '
*     I_STRUCTURE_NAME   =
*     I_BACKGROUND_ID    = ' '
*     I_GRID_TITLE       =
*     I_GRID_SETTINGS    =
      is_layout_lvc      = wa_layout
      it_fieldcat_lvc    = lt_fldcat "it_fieldcat
    TABLES
      t_outtab           = lt_msg
    EXCEPTIONS
      program_error      = 1
      OTHERS             = 2.
  IF sy-subrc <> 0.
* Implement suitable error handling here
  ENDIF.
ENDFORM.

Main Program :  

REPORT ZBDC_UPLOAD.

INCLUDE ZFI_VEN_ADV_UPLD_TOP.
INCLUDE ZFI_VEN_ADV_UPLD_SEL.
INCLUDE ZFI_VEN_ADV_UPLD_FORM.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR filename.
  PERFORM file_f4 CHANGING filename.

START-OF-SELECTION.
  PERFORM tab_file_upd.
  PERFORM bdc_open.
  PERFORM display_log.