OPEN SQL:
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.
NATIVE SQL:
* Use a native SQL call with ADBC here to retrieve the joined
* Step 1: Construct the native SQL string into local variable lv_sql.
* Make sure to order by business partner ID
* Step 2: Instantiate the SQL connection and the SQL statement objects
* Step 3: Execute the native SQL query
* Step 4: Read the result into the internal table lt_partner
* Step 5: close resources, i.e. the SQL statement and connection
* 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.
*&---------------------------------------------------------------------*
*& 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. "
and display the data:
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.
1) referance for SQL ststements
2) Referance for the connection
3) Referance for the instantiation
4) read the data withh standard methos
5) close the connection
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.
NATIVE SQL:
* Use a native SQL call with ADBC here to retrieve the joined
* Step 1: Construct the native SQL string into local variable lv_sql.
* Make sure to order by business partner ID
* Step 2: Instantiate the SQL connection and the SQL statement objects
* Step 3: Execute the native SQL query
* Step 4: Read the result into the internal table lt_partner
* Step 5: close resources, i.e. the SQL statement and connection
* 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.
*&---------------------------------------------------------------------*
*& 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. "
and display the data:
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.
1) referance for SQL ststements
2) Referance for the connection
3) Referance for the instantiation
4) read the data withh standard methos
5) close the connection
No comments:
Post a Comment