ABAP HANA Example for Native sql in ABAP Editor

*&---------------------------------------------------------------------*
*& Report  HA400_OPTIMIZE_OSQL_S1
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT ha400_native_sql_s1 MESSAGE-ID ha400.

* Structure for Result: list of customers with age of their sales orders
TYPES: BEGIN OF ts_customer,
         id              TYPE snwd_bpa-bp_id,
         name            TYPE snwd_bpa-company_name,
         avg_days_open   TYPE i,
       END OF ts_customer.

TYPES: tt_customer TYPE STANDARD TABLE OF ts_customer
                       WITH NON-UNIQUE KEY id, company_name.

* Data objects
DATA: gt_customers   TYPE tt_customer,
      gt_customers_t TYPE tt_customer.


* Database connection
DATA gv_dbcon TYPE dbcon-con_name.
CONSTANTS:
     c_primdb TYPE dbcon-con_name VALUE 'DEFAULT',
     c_secdb  TYPE dbcon-con_name VALUE 'HANADB'.

* selection screen
SELECTION-SCREEN BEGIN OF BLOCK exe WITH FRAME TITLE text-exe.
PARAMETERS:
  pa_temp   TYPE xfeld RADIOBUTTON GROUP exe DEFAULT 'X',
  pa_solu   TYPE xfeld RADIOBUTTON GROUP exe,
  pa_both   TYPE xfeld RADIOBUTTON GROUP exe.
SELECTION-SCREEN END OF BLOCK exe.

SELECTION-SCREEN BEGIN OF BLOCK dbc WITH FRAME TITLE text-dbc.
PARAMETERS:
  pa_prim   TYPE xfeld RADIOBUTTON GROUP dbc,
  pa_sec    TYPE xfeld RADIOBUTTON GROUP dbc DEFAULT 'X'.
SELECTION-SCREEN END OF BLOCK dbc.

START-OF-SELECTION.

  CASE 'X'.
    WHEN pa_prim.
      gv_dbcon = c_primdb.
    WHEN pa_sec.
      gv_dbcon = c_secdb.
  ENDCASE.


  CASE 'X'.
    WHEN pa_temp.  " template only
      PERFORM get_data_template USING    gv_dbcon
                                CHANGING gt_customers_t.
      PERFORM display USING gt_customers_t.
    WHEN pa_solu.  " solution only
      PERFORM get_data_solution USING    gv_dbcon
                                CHANGING gt_customers.
      PERFORM display USING gt_customers.
    WHEN pa_both.  " both subroutines
      PERFORM get_data_solution USING    gv_dbcon
                                CHANGING gt_customers.
      PERFORM get_data_template USING    gv_dbcon
                                CHANGING gt_customers_t.
      IF gt_customers <> gt_customers_t.
        MESSAGE i010.
      ELSE.
        MESSAGE s011.
      ENDIF.
  ENDCASE.

*&---------------------------------------------------------------------*
*&      Form  get_data_template
*&---------------------------------------------------------------------*
FORM get_data_template  USING pv_dbcon     TYPE dbcon-con_name
                        CHANGING ct_customers TYPE tt_customer.
* Declarations
****************
* Type for target fields
  TYPES: BEGIN OF tty_customer_invoice,
           id              TYPE snwd_bpa-bp_id,
           name            TYPE snwd_bpa-company_name,
           created_at      TYPE snwd_so_inv_head-created_at,
         END OF tty_customer_invoice.

* local structure and work areas
  DATA: ls_customer         LIKE LINE OF ct_customers,
        ls_customer_invoice TYPE         tty_customer_invoice.

* Targets for Select
  DATA lt_customer_invoice TYPE SORTED TABLE OF tty_customer_invoice WITH NON-UNIQUE KEY id.

* help variables
  DATA: lv_current_date TYPE timestampl,
        lv_count TYPE i.

*  processing
****************

  CLEAR ct_customers.
  CLEAR lv_count.

  " get current date (UTC)
  GET TIME STAMP FIELD lv_current_date.


  SELECT bp~bp_id AS bp_id
         bp~company_name AS bp_name
         inv~created_at AS created_at
    FROM snwd_so_inv_head AS inv JOIN snwd_bpa AS bp ON inv~buyer_guid = bp~node_key
    CONNECTION (pv_dbcon)
    INTO TABLE lt_customer_invoice
    WHERE inv~payment_status <> 'P'
    ORDER BY bp~bp_id.


  LOOP AT lt_customer_invoice INTO ls_customer_invoice.

    IF sy-tabix = 1.
      ls_customer-id       = ls_customer_invoice-id.
      ls_customer-name     = ls_customer_invoice-name.

    ELSEIF ls_customer_invoice-id <> ls_customer-id.

      ls_customer-avg_days_open = ls_customer-avg_days_open /  lv_count.
      INSERT ls_customer INTO TABLE ct_customers.

      CLEAR ls_customer.
      CLEAR lv_count.

      ls_customer-id       = ls_customer_invoice-id.
      ls_customer-name     = ls_customer_invoice-name.

    ENDIF.
    lv_count = lv_count + 1.
    ls_customer-avg_days_open = ls_customer-avg_days_open
          + cl_abap_tstmp=>subtract( tstmp1 = lv_current_date
                                     tstmp2 = ls_customer_invoice-created_at ) / 86400.

  ENDLOOP.

  ls_customer-avg_days_open = ls_customer-avg_days_open /  lv_count.
  INSERT ls_customer INTO TABLE ct_customers.

*  SORT ct_customers BY id. "already sorted

ENDFORM.                    "

*&---------------------------------------------------------------------*
*&      Form  get_data_solution
*&---------------------------------------------------------------------*
FORM get_data_solution USING pv_dbcon     TYPE dbcon-con_name
                       CHANGING ct_customers TYPE tt_customer.

* analyze, then improve code here

* Declarations
****************

  TYPES: BEGIN OF lts_partner,
           bp_id        TYPE snwd_bpa-bp_id,
           company_name TYPE snwd_bpa-company_name,
           created_at   TYPE snwd_so_inv_head-created_at,
         END OF lts_partner.

* Work Area for Result
  DATA ls_customer LIKE LINE OF ct_customers.

* Targets for Select
  DATA: ls_partner TYPE lts_partner,
        lt_partner TYPE STANDARD TABLE OF lts_partner.

* ADBC Objects and variables
  DATA:  lo_sql_stmt TYPE REF TO cl_sql_statement,
         lo_conn     TYPE REF TO cl_sql_connection,
         lo_result   TYPE REF TO cl_sql_result_set,
         lv_sql      TYPE string,
         lr_data     TYPE REF TO data.

* Eception handling
  DATA:   lx_sql_exc TYPE REF TO cx_sql_exception,
          lv_text TYPE string.

* help variables
  DATA: lv_current_date TYPE timestampl,
        lv_count TYPE i.

*  processing
****************

  TRY.
      CLEAR ct_customers.
      CLEAR lv_count.

      " get current date (UTC)
      GET TIME STAMP FIELD lv_current_date.
*     Step 1: Construct the native SQL string into local variable lv_sql.
*             Make sure to order by business partner ID
      CONCATENATE 'SELECT BP.BP_ID, BP.COMPANY_NAME, INV.CREATED_AT'
                  '  FROM SNWD_BPA AS BP INNER JOIN SNWD_SO_INV_HEAD AS INV'
                  '                              ON BP.NODE_KEY = INV.BUYER_GUID'
                  ' WHERE INV.CLIENT = '''
                  sy-mandt
                  ''' AND PAYMENT_STATUS <> ''P'''
                  '  ORDER BY BP.BP_ID'
             INTO lv_sql.
**     Alternative using String templates and expressions:
*      lv_sql = | SELECT BP.BP_ID, BP.COMPANY_NAME, INV.CREATED_AT |
*            && |   FROM SNWD_BPA AS BP INNER JOIN SNWD_SO_INV_HEAD AS INV |
*            && |                           ON BP.NODE_KEY = INV.BUYER_GUID |
*            && |  WHERE iNV.CLIENT = '{ SY-MANDT }' AND PAYMENT_STATUS <> 'P' |
*            && |  ORDER BY BP.BP_ID |.

*     Step 2: Instantiate the SQL connection and the SQL statement objects

*     Get a secondary DB connection
      lo_conn = cl_sql_connection=>get_connection( pv_dbcon ).
*     Create an SQL statement to be executed via the connection
      lo_sql_stmt = lo_conn->create_statement( ).

**     ALTERNATIVE: Do both in one step.
*      CREATE OBJECT lo_sql_stmt EXPORTING con_ref = cl_sql_connection=>get_connection( pv_dbcon ).
**     ALTERNATIVE: Don't even pass a secondary connection if the "default" connection is used anyway
*      CREATE OBJECT lo_sql_stmt.

*     use method set_table_name_for_trace( ... ) to tell SQL Trace which table is accessed
      lo_sql_stmt->set_table_name_for_trace( 'SNWD_SO join SNWD_BPA' ).

*     Step 3: Execute the native SQL query
      lo_result = lo_sql_stmt->execute_query( lv_sql ).

*     Step 4: Read the result into the internal table lt_partner
      GET REFERENCE OF lt_partner INTO lr_data.
      lo_result->set_param_table( lr_data ).
      lo_result->next_package( ).

*     Step 5: close resources, i.e. the SQL statement and connection
      lo_result->close( ).
      lo_conn->close( ).

*     Loop over result as in Open SQL version, to calculate the average sales order ages
      LOOP AT lt_partner INTO ls_partner.

        IF sy-tabix = 1. " First customer, first order

          ls_customer-id       = ls_partner-bp_id.
          ls_customer-name     = ls_partner-company_name.
          CLEAR: ls_customer-avg_days_open.

        ELSEIF ls_customer-id <> ls_partner-bp_id. "First order of next customer
          ls_customer-avg_days_open = ls_customer-avg_days_open / lv_count.
          INSERT ls_customer INTO TABLE ct_customers.

          ls_customer-id       = ls_partner-bp_id.
          ls_customer-name     = ls_partner-company_name.
          CLEAR: ls_customer-avg_days_open,
                 lv_count.


        ENDIF.
        lv_count = lv_count + 1.
        ls_customer-avg_days_open = ls_customer-avg_days_open
                      + cl_abap_tstmp=>subtract( tstmp1 = lv_current_date
                                                 tstmp2 = ls_partner-created_at ) / 86400.

      ENDLOOP.
     ls_customer-avg_days_open = ls_customer-avg_days_open / lv_count.
     INSERT ls_customer INTO TABLE ct_customers. " Don't forget last customer

    CATCH cx_sql_exception INTO lx_sql_exc.
      lv_text = lx_sql_exc->get_text( ).
      MESSAGE lv_text TYPE 'E'.

  ENDTRY.

ENDFORM.                    "

*&---------------------------------------------------------------------*
*&      Form  output
*&---------------------------------------------------------------------*

FORM display USING it_customers TYPE tt_customer.

  DATA: lo_alv  TYPE REF TO cl_salv_table,
        lx_msg  TYPE REF TO cx_salv_msg,
        lv_text TYPE string.

* display
  TRY.
      cl_salv_table=>factory(
          IMPORTING
            r_salv_table = lo_alv
          CHANGING
            t_table      = it_customers ).

      lo_alv->display( ).

    CATCH cx_salv_msg INTO lx_msg.
      lv_text = lx_msg->get_text( ).
      MESSAGE lv_text TYPE 'E'.
  ENDTRY.

ENDFORM.