[ABAP] Native SQL Join via ADBC(ABAP Database Connectivity) with HANA DB

*&---------------------------------------------------------------------*
*& Report  ZHA400_01_OSQL_HANA_6
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  zha400_01_osql_hana_6.

CONSTANTS c_con TYPE string VALUE 'SFLIGHT'.

TYPES :  BEGIN OF   ty_s_customers,
                     mandt TYPE scustom-mandt,
                     id TYPE scustom-id,
                     name TYPE scustom-name,
                     postcode TYPE scustom-postcode,
                     city TYPE scustom-city,
                     country TYPE scustom-country,
                     fldate TYPE sbook-fldate,
                 END OF  ty_s_customers,
                 ty_t_customers TYPE TABLE OF ty_s_customers.

DATA: gs_customers TYPE ty_s_customers,
            gt_customers LIKE TABLE OF gs_customers.

*PERFORM get_data_template
PERFORM get_data_solution
                    CHANGING gt_customers .

LOOP AT gt_customers INTO gs_customers.
  WRITE:/ gs_customers-mandt, gs_customers-id, gs_customers-name, gs_customers-postcode,
                  gs_customers-city, gs_customers-country, gs_customers-fldate.
  CLEAR gs_customers.
ENDLOOP.

*&---------------------------------------------------------------------*
*&      Form  GET_DATA_TEMPLATE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      <--P_CT_CUSTOMERS  text
*----------------------------------------------------------------------*
FORM get_data_template
            CHANGING ct_customers TYPE ty_t_customers.

  CLEAR ct_customers.

  SELECT  id name postcode city country
      FROM scustom CONNECTION (c_con)
        INTO TABLE ct_customers.

ENDFORM.                    " GET_DATA_TEMPLATE

 

*&---------------------------------------------------------------------*
*&      Form  GET_DATA_SOLUTION
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      <--P_CT_CUSTOMERS  text
*----------------------------------------------------------------------*
FORM get_data_solution
    CHANGING ct_customers TYPE ty_t_customers.
  DATA: lo_con TYPE REF TO cl_sql_connection,
        lo_sql TYPE REF TO cl_sql_statement,
        lo_result TYPE REF TO cl_sql_result_set,
        lv_sql TYPE string,
        lt_customer TYPE TABLE OF scustom, "MODIFY 1"
        lr_data TYPE REF TO data,
        lr_root TYPE REF TO cx_root,
        lv_text TYPE string.

  CLEAR ct_customers.

  TRY.
      lo_con = cl_sql_connection=>get_connection('HANA').

      CREATE OBJECT lo_sql
        EXPORTING
          con_ref = lo_con.

      "** MODIFY 2 **"
      lv_sql = | SELECT C.MANDT, C.ID, C.NAME, C.POSTCODE, C.CITY, C.COUNTRY, B.FLDATE | &&
               | FROM SCUSTOM AS C INNER JOIN SBOOK AS B | &&
               | ON C.ID = B.CUSTOMID | &&
               | WHERE C.MANDT = '001' | &&
               |   AND B.CANCELLED = { SPACE } | &&
               || .

      lo_result = lo_sql->execute_query( lv_sql ).
      "** MODIFY 3 **"
      GET REFERENCE OF ct_customers INTO lr_data.
      lo_result->set_param_table( lr_data ).
      lo_result->next_package( ).
      lo_result->close( ).
    CATCH cx_root INTO lr_root.
      lv_text = lr_root->get_text( ).
      MESSAGE lv_text TYPE 'E'.
  ENDTRY.
ENDFORM.                    "get_data_solution