|
*&---------------------------------------------------------------------*
*& Report ZHA400_01_PROJECT_1
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT zha400_01_project_1.
TYPES : BEGIN OF ty_s_list,
carrid TYPE scarr-carrid,
carrname TYPE scarr-carrname,
year(4),
loccurkey TYPE sbook-loccurkey,
loccuram TYPE sbook-loccuram,
END OF ty_s_list,
ty_t_list TYPE TABLE OF ty_s_list.
DATA: gs_list TYPE ty_s_list,
gt_list LIKE TABLE OF gs_list.
SELECT-OPTIONS: so_year FOR gs_list-year NO-EXTENSION.
INITIALIZATION.
so_year-sign = 'I'.
so_year-option = 'BT'.
so_year-high = sy-datum+0(4).
so_year-low = so_year-high - 1.
APPEND so_year.
START-OF-SELECTION.
PERFORM get_data_solution
CHANGING gt_list .
SORT gt_list BY carrid year loccurkey.
WRITE: 0 'Year', 8 'Airline', 16 'Airline Name', 35 'Local Currency',
45 'Price Sum'.
ULINE.
LOOP AT gt_list INTO gs_list.
WRITE:/ gs_list-year UNDER 'Year' , gs_list-carrid UNDER 'Airline',
gs_list-carrname UNDER 'Airline Name', gs_list-loccurkey UNDER 'Local Currency',
gs_list-loccuram UNDER 'Price Sum'.
CLEAR gs_list.
ENDLOOP.
*&---------------------------------------------------------------------*
*& Form GET_DATA_TEMPLATE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* <--P_CT_CUSTOMERS text
*----------------------------------------------------------------------*
FORM get_data_solution
CHANGING ct_list TYPE ty_t_list.
* Declarations
*********************
* Types for target fields
* TYPES: BEGIN OF lty_s_scarr,
* carrid TYPE scarr-carrid,
* carrname TYPE scarr-carrname,
* END OF lty_s_scarr.
*
* TYPES: BEGIN OF lty_s_book,
* carrid TYPE sbook-carrid,
* order_date TYPE sbook-order_date,
* cancelled TYPE sbook-cancelled,
* loccurkey TYPE sbook-loccurkey,
* loccuram TYPE sbook-loccuram,
* END OF lty_s_book.
*
* TYPES: BEGIN OF lty_s_book_count,
* carrid TYPE sbook-carrid,
* year(4),
* loccurkey TYPE sbook-loccurkey,
* loccuram TYPE sbook-loccuram,
* END OF lty_s_book_count.
* Work Area for Result
DATA: ls_list LIKE LINE OF ct_list,
lv_begda(10) TYPE c,
lv_endda(10) TYPE c.
*
** Targets for Select
* DATA: lt_scarr TYPE SORTED TABLE OF scarr
* WITH NON-UNIQUE KEY carrid,
* ls_scarr TYPE scarr,
*
* lt_sbook TYPE SORTED TABLE OF lty_s_book
* WITH NON-UNIQUE KEY carrid order_date,
* ls_sbook TYPE lty_s_book,
* lt_sbook_count TYPE TABLE OF lty_s_book_count,
** WITH NON-UNIQUE KEY agencynum carrid year,
* ls_sbook_count TYPE lty_s_book_count,
* ls_sbook_count_t TYPE lty_s_book_count,
* lv_year(4),
* lv_tabix TYPE sy-tabix,
* lv_rate TYPE p DECIMALS 1.
*
** help variables
* DATA lv_count TYPE i.
* Processing
*****************************
*************************************************
* NEW SAP HANA DB ADBC WITH NATIVE SQL
*************************************************
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.
CONCATENATE so_year-low '-01-01' INTO lv_begda.
CONCATENATE so_year-high '-12-31' INTO lv_endda.
CLEAR ct_list.
TRY.
lo_con = cl_sql_connection=>get_connection('HANA').
CREATE OBJECT lo_sql
EXPORTING
con_ref = lo_con.
"** MODIFY 2 **"
lv_sql = | SELECT | &&
| C.CARRID, | &&
| C.CARRNAME, | &&
| YEAR(B.ORDER_DATE) AS YEAR, | &&
| B.LOCCURKEY, | &&
| SUM( B.LOCCURAM ) AS LOCCURAM | &&
| FROM SCARR AS C | &&
| INNER JOIN ZSBOOK1 AS B | &&
| ON C.MANDT = B.MANDT | &&
| AND C.CARRID = B.CARRID | &&
| WHERE C.MANDT = { sy-mandt } | &&
| AND B.ORDER_DATE BETWEEN '{ lv_begda }' AND '{ lv_endda }' | &&
| AND B.CANCELLED = '{ space }' | &&
| GROUP BY C.CARRID, C.CARRNAME, YEAR(B.ORDER_DATE), B.LOCCURKEY | &&
|| .
lo_result = lo_sql->execute_query( lv_sql ).
"** MODIFY 3 **"
GET REFERENCE OF ct_list 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.
*************************************************
* ORIGINAL SAP OPENQUERY
*************************************************
* SELECT carrid carrname
* FROM scarr
* INTO CORRESPONDING FIELDS OF TABLE lt_scarr.
*
* CONCATENATE so_year-low '-01-01' INTO lv_begda.
* CONCATENATE so_year-high '-12-31' INTO lv_endda.
*
* SELECT carrid order_date cancelled loccuram loccurkey
* FROM zsbook1
* INTO CORRESPONDING FIELDS OF TABLE lt_sbook
* WHERE cancelled <> 'X'
* AND order_date BETWEEN lv_begda AND lv_endda.
*
*
* LOOP AT lt_sbook INTO ls_sbook.
* ls_sbook_count-carrid = ls_sbook-carrid.
* ls_sbook_count-year = ls_sbook-order_date+0(4).
* ls_sbook_count-loccurkey = ls_sbook-loccurkey.
* ls_sbook_count-loccuram = ls_sbook-loccuram.
* COLLECT ls_sbook_count INTO lt_sbook_count.
* CLEAR: ls_sbook_count, ls_sbook.
* ENDLOOP.
*
* SORT lt_sbook_count BY carrid year loccurkey.
*
* LOOP AT lt_sbook_count INTO ls_sbook_count.
*
* MOVE-CORRESPONDING ls_sbook_count TO ls_list.
* CLEAR ls_scarr.
* READ TABLE lt_scarr WITH KEY carrid = ls_list-carrid INTO ls_scarr.
* ls_list-carrname = ls_scarr-carrname.
*
* APPEND ls_list TO ct_list.
*
* CLEAR: ls_sbook_count, ls_list..
* ENDLOOP.
*************************************************
ENDFORM. " GET_DATA_TEMPLATE
| 
