*&---------------------------------------------------------------------*
*& Report ZHA400_01_OSQL_HANA_11
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT ZHA400_01_OSQL_HANA_11.
CONSTANTS c_con TYPE dbcon_name VALUE 'SFLIGHT'.
TYPES : BEGIN OF ty_s_customers,
id TYPE scustom-id,
name TYPE scustom-name,
postcode TYPE scustom-postcode,
city TYPE scustom-city,
country TYPE scustom-country,
days_ahead TYPE i,
END OF ty_s_customers,
ty_t_customers TYPE TABLE OF ty_s_customers.
DATA: gs_cust_early TYPE ty_s_customers,
gt_cust_early LIKE TABLE OF gs_cust_early,
gs_cust_late TYPE ty_s_customers,
gt_cust_late LIKE TABLE OF gs_cust_late.
PERFORM get_data_solution USING '100'
CHANGING gt_cust_early gt_cust_late .
WRITE:/ 'ET_EARLY_BIRDS'.
LOOP AT gt_cust_early INTO gs_cust_early.
WRITE:/ gs_cust_early-id, gs_cust_early-name, gs_cust_early-postcode,
gs_cust_early-city, gs_cust_early-country, gs_cust_early-days_ahead.
CLEAR gs_cust_early.
ENDLOOP.
NEW-LINE. SKIP.
WRITE:/ 'ET_LAST_MINUTE'.
LOOP AT gt_cust_late INTO gs_cust_late.
WRITE:/ gs_cust_late-id, gs_cust_late-name, gs_cust_late-postcode,
gs_cust_late-city, gs_cust_late-country, gs_cust_late-days_ahead.
CLEAR gs_cust_late.
ENDLOOP.
*&---------------------------------------------------------------------*
*& Form GET_DATA_SOLUTION
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* <--P_CT_CUSTOMERS text
*----------------------------------------------------------------------*
FORM get_data_solution USING pv_number TYPE i
CHANGING ct_cust_early TYPE ty_t_customers
ct_cust_late TYPE ty_t_customers.
* Declarations
*********************
DATA: lv_count TYPE i.
* ADBC Objects and Variables
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,
lr_data TYPE REF TO data.
* Type for result overview
TYPES: BEGIN OF lty_s_ov,
param TYPE string,
value TYPE string,
END OF lty_s_ov.
* Data object for result overview
DATA: lt_ov TYPE TABLE OF lty_s_ov,
ls_ov TYPE lty_s_ov.
* Exception Handling
DATA: lx_sql_exc TYPE REF TO cx_sql_exception,
lv_text TYPE string.
* Processing
************************************************************
CLEAR: ct_cust_early,
ct_cust_late.
TRY.
* Get secondary DB Connection.
lo_con = cl_sql_connection=>get_connection( c_con ).
* Create statement
CREATE OBJECT lo_sql
EXPORTING
con_ref = lo_con.
lv_sql = | CALL _SYS_BIC."yha400.demo/SP_EARLY_BIRD_AND_LAST_MINUTE" | &&
| ( { pv_number }, { sy-mandt }, null, null ) WITH OVERVIEW |.
* Execute Query
lo_result = lo_sql->execute_query( lv_sql ).
* Read result into internal Table
GET REFERENCE OF lt_ov INTO lr_data.
lo_result->set_param_table( lr_data ).
lo_result->next_package( ).
lo_result->close( ).
* 1. retrieve
"** Modify 1 **"
READ TABLE lt_ov INTO ls_ov WITH KEY param = 'ET_EARLY_BIRDS'.
lv_sql = | SELECT * FROM { ls_ov-value } |.
lo_result = lo_sql->execute_query( lv_sql ).
"** Modify 2 **"
GET REFERENCE OF ct_cust_early INTO lr_data.
lo_result->set_param_table( lr_data ).
lo_result->next_package( ).
lo_result->close( ).
* 2. retrieve
"** Modify 3 **"
READ TABLE lt_ov INTO ls_ov WITH KEY param = 'ET_LAST_MINUTE'.
lv_sql = | SELECT * FROM { ls_ov-value } |.
lo_result = lo_sql->execute_query( lv_sql ).
"** Modify 4 **"
GET REFERENCE OF ct_cust_late INTO lr_data.
lo_result->set_param_table( lr_data ).
lo_result->next_package( ).
lo_result->close( ).
CATCH cx_sql_exception INTO lx_sql_exc. " Exception Class for SQL Error.
lv_text = lx_sql_exc->get_text( ).
MESSAGE lv_text TYPE 'E'.
ENDTRY.
ENDFORM. " GET_DATA_SOLUTION
*&---------------------------------------------------------------------*
*& Form GET_DATA_TEMPLATE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* <--P_CT_CUSTOMERS text
*----------------------------------------------------------------------*
FORM get_data_template USING pv_number TYPE i
CHANGING ct_cust_early TYPE ty_t_customers
ct_cust_late TYPE ty_t_customers.
* Declarations
*********************
DATA: lv_count TYPE i.
* ADBC Objects and Variables
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,
lr_data TYPE REF TO data.
* Exception Handling
DATA: lx_sql_exc TYPE REF TO cx_sql_exception,
lv_text TYPE string.
* Processing
************************************************************
CLEAR: ct_cust_early,
ct_cust_late.
TRY.
* Get secondary DB Connection.
lo_con = cl_sql_connection=>get_connection( c_con ).
* Create statement
CREATE OBJECT lo_sql
EXPORTING
con_ref = lo_con.
* create SQL statement.
lv_sql = | SELECT TOP { pv_number } | &&
| ID, NAME, POSTCODE, CITY, COUNTRY, AVG( DAYS_AHEAD ) AS DAYS_AHEAD | &&
| FROM _SYS_BIC."yha400.demo/AT_DAYS_AHEAD" | &&
| WHERE MANDT = { sy-mandt } | &&
| GROUP BY MANDT, ID, NAME, POSTCODE, CITY, COUNTRY | &&
| ORDER BY MANDT, DAYS_AHEAD DESC | .
* Execute Query
lo_result = lo_sql->execute_query( lv_sql ).
* Read result into internal Table
GET REFERENCE OF ct_cust_early INTO lr_data.
lo_result->set_param_table( lr_data ).
lo_result->next_package( ).
lo_result->close( ).
* create SQL statement.
lv_sql = | SELECT TOP { pv_number } | &&
| ID, NAME, POSTCODE, CITY, COUNTRY, AVG( DAYS_AHEAD ) AS DAYS_AHEAD | &&
| FROM _SYS_BIC."yha400.demo/AT_DAYS_AHEAD" | &&
| WHERE MANDT = { sy-mandt } | &&
| GROUP BY MANDT, ID, NAME, POSTCODE, CITY, COUNTRY | &&
| ORDER BY MANDT, DAYS_AHEAD ASC | .
* Execute Query
lo_result = lo_sql->execute_query( lv_sql ).
* Read result into internal Table
GET REFERENCE OF ct_cust_late INTO lr_data.
lo_result->set_param_table( lr_data ).
lo_result->next_package( ).
lo_result->close( ).
CATCH cx_sql_exception INTO lx_sql_exc. " Exception Class for SQL Error.
lv_text = lx_sql_exc->get_text( ).
MESSAGE lv_text TYPE 'E'.
ENDTRY.
ENDFORM. " GET_DATA_TEMPLATE
'SAP > ABAP' 카테고리의 다른 글
| [ABAP] VIEW 에 파라미터 넣는 Native SQL 예제 (0) | 2013.06.13 |
|---|---|
| [ABAP] Native SQL Join via ADBC(ABAP Database Connectivity) with HANA DB (0) | 2013.06.13 |
| [ABAP] REPORT ZHANA01_04. Global Class 의 Attribute 및 Method 사용 예제 (0) | 2013.06.11 |
| [ABAP] 데이터 타입 (0) | 2013.06.11 |
| [ABAP] REPORT ZHANA01_02. 응용한 subroutine 예제 (0) | 2013.06.11 |