*&---------------------------------------------------------------------*
*&      Form  get_data_solution
*&---------------------------------------------------------------------*
FORM get_data_solution CHANGING ct_customers TYPE ty_t_customers.



* ADBC Objects and Variables

* DATA:
*    lv_sql TYPE string.

* Processing
****************************

* 1. Get DB Connection (optional for accessing primary database)
  TRY.
      cl_sql_connection=>get_connection(
        EXPORTING
          con_name         = c_primdb
          sharable         = space    " allgemeines flag
        RECEIVING
          con_ref          =  DATA(lo_conn)
      ).
    CATCH cx_sql_exception.    "
  ENDTRY.

* 2. Create statement object
  DATA(lo_statement) = NEW cl_sql_statement(
      con_ref            = lo_conn
*    tab_name_for_trace =
  ).

* 3. assemble SQL statement

  CONCATENATE `SELECT `
               `C.ID, `
               `C.NAME, `
               `C.POSTCODE, `
               `C.CITY, `
               `C.COUNTRY, `
               `AVG( DAYS_BETWEEN( B.ORDER_DATE, B.FLDATE ) ) AS days_ahead `
               `FROM SBOOK AS B INNER JOIN SCUSTOM AS C `
               `ON B.MANDT = C.MANDT `
               `AND B.CUSTOMID = C.ID `
               `WHERE B.CANCELLED <> 'X' `
               `AND C.MANDT = `
                `'`
                sy-mandt
                `'`
                `GROUP BY C.ID, C.NAME, C.POSTCODE, C.CITY, C.COUNTRY `
               `ORDER BY C.ID `
          INTO DATA(lv_sql).

* alternative use string templates and string expressions
*  lv_sql = |SELECT |       &&
*           |C.ID, |        &&
*           |C.NAME, |      &&
*           |C.POSTCODE,|   &&
*           |C.CITY,  |     &&
*           |C.COUNTRY, |   &&
*           |AVG( DAYS_BETWEEN( B.ORDER_DATE, B.FLDATE ) ) AS days_ahead |    &&
*           |FROM SBOOK AS B INNER JOIN SCUSTOM AS C | &&
*           |ON B.MANDT = C.MANDT | &&
*           |AND B.CUSTOMID = C.ID | &&
*           |WHERE C.MANDT = '{ sy-mandt }' | &&
*           |AND B.CANCELLED <> 'X' | &&
*           |GROUP BY C.ID, C.NAME, C.POSTCODE, C.CITY, C.COUNTRY | &&
*           |ORDER BY C.ID |.

* 4. Execute Query
  TRY.
      DATA(lo_result) = lo_statement->execute_query( lv_sql ).


* 5. Assign target variable
      DATA: lr_data   TYPE REF TO data.
      GET REFERENCE OF ct_customers INTO lr_data.
      lo_result->set_param_table( lr_data ).

* 6. Read result into internal Table
      lo_result->next_package( ).

* 7. Close connection
      lo_result->close( ).
    CATCH cx_sql_exception.
  ENDTRY.
ENDFORM.