SAP HANA Example 1 with Primary and Secondary Database using nested select

REPORT ZHA400_13_SEC_DB1  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.

* Data objects
DATA: gt_customers TYPE tt_customer,
gt_customers_t TYPE tt_customer.
DATA: lv_start TYPE timestampl,
lv_end TYPE timestampl,
lv_message TYPE string.

* 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
****************

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

* Targets for Select
DATA: ls_partner TYPE snwd_bpa,
ls_invoice TYPE snwd_so_inv_head.

* 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 * FROM snwd_bpa INTO ls_partner.

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

SELECT * FROM snwd_so_inv_head
INTO ls_invoice
WHERE buyer_guid = ls_partner-node_key
AND payment_status <> 'P'.

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

ENDSELECT.
IF ls_customer-avg_days_open <> 0.
ls_customer-avg_days_open = ls_customer-avg_days_open / lv_count.
INSERT ls_customer INTO TABLE ct_customers.
ENDIF.

ENDSELECT.

SORT ct_customers BY id.

ENDFORM. "

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


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.