Consuming HANA Views, Procedures, External Views in ABAP 7.40 Syntax – Part 2

Part 1: http://scn.sap.com/community/abap/hana/blog/2014/01/08/consuming-hana-views-procedures-external-views-in-abap-740-syntax–part-1

• ABAP Report with new data declaration syntaxes on 7.40
• ABAP Report on HANA using ADBC

Part 2:http://scn.sap.com/community/abap/hana/blog/2014/01/08/consuming-hana-views-procedures-external-views-in-abap-740-syntax–part-2

• Consuming Attribute View using External View.
• Consuming Attribute View using Native SQL
• Consuming Analytic View/Calculation View in ABAP

Part 3: http://scn.sap.com/community/abap/hana/blog/2014/01/08/as

• Consuming HANA artifact Stored Procedure using ABAP Proxy Procedure.
• Consume HANA artifact Stored Procedure by Calling it in ABAP Code.

T3. Consuming Attribute View using External View.

Step 1:  Create HANA Attribute view :

Step 2: Save and Activate the view and check the data by using ‘Data Preview’ option.

Steps to create external view:

Step 1: Go to ‘ABAP’ prospective.

Step 2:  Right click on ABAP package under which you want to create this external view. Under ‘New’ click on ‘Other ABAP Repository Object’

Step 3: Expand ‘Dictionary’ folder and click on ‘Dictionary View’.

Step 4: Enter name and description of view and select ‘External View’ radio button and browse and select your HANA view.

Step 5: Click on Next, Finish and then activate the view, this will create your external view in you ABAP system, you can cross check in SE11. ‘Synchronize’ button should be used if any changes are made in HANA view

Source Code to consume the above created external View:

* External View
DATA:  lt_tab TYPE TABLE OF external_view.

LOOP AT lt_tab ASSIGNING FIELD–SYMBOL(<fs>).
WRITE: / ‘Pernr:’ ,<fs>–pernr.
WRITE: ‘=’, <fs>–last_rev_bill_date, /.
ENDLOOP.

Output:

T4. Consuming Attribute View using Native SQL

  DATA:  lt_tab2 TYPE TABLE OF external_view.

* consuming attribute view

  TRY.

      lv_sql = | SELECT bill_rate, emp_name, bill_date, pernr, |

*           use HANA built-in function

            && | DAYS_BETWEEN(BILL_DATE,CURRENT_UTCDATE) AS LAST_BILL_REV |

            && |   FROM _SYS_BIC.”mohas97_ha5/AT_EMP_BILL” |.

*     Create an SQL statement to be executed via default secondary DB connection

      CREATE OBJECT lo_sql_stmt EXPORTING con_ref = cl_sql_connection=>get_connection( ).

*     execute the native SQL query/ SQL Call

      lo_result = NEW cl_sql_statement( )->execute_query( lv_sql ).   ” new syntax

*     read the result into the internal table lt_partner

      GET REFERENCE OF lt_tab2 INTO lr_data.

lo_result->set_param_table( lr_data ).  “Retrieve result of native SQL call

lo_result->next_package( ).

lo_result->close( ).

    CATCH cx_sql_exception INTO lx_sql_exc.

      lv_text = lx_sql_exc->get_text( ).

      MESSAGE lv_text TYPE ‘E’.

  ENDTRY.

  LOOP AT lt_tab2 ASSIGNING FIELD-SYMBOL(<fs>).

    WRITE: / ‘Pernr:’ ,<fs>-pernr.

    WRITE: ‘=’, <fs>-last_rev_bill_date, /.

  ENDLOOP.

T5. Consuming Analytic View/Calculation View in ABAP

Calculation view can also be consumed in the same way.

Step 1:  Create HANA Analytic view

Step 2: Save and Activate the view and check the data by using ‘Data Preview’ option.

Source Code to consume the above Analytic View:

* consuming analytic view with input parameter

  DATA: LT_PROJ TYPE ZTT_EMP_PROJ.

  TRY.

      lv_sql = | SELECT mandt, PERNR, PROJ_NAME, RESOURCE_NO |

             && |   FROM _SYS_BIC.”mohas97_ha5/AN_EMP_PROJ” |

*            && |  (‘PLACEHOLDER’=(‘$$IP_PERNR$$’, ‘ { lv_pernr } ‘ ) ) |

            && |  WHERE mandt = { sy-mandt } |.

*            && |  ORDER BY bill_rate |.

Source Code for Using Input Parameter

*      lv_sql = | SELECT mandt, PERNR, BILL_RATE, BILL_DATE |

*             && |   FROM _SYS_BIC.”mohas97_ha5/AN_BILL_DATE” |

*            && |  (‘PLACEHOLDER’ = (‘$$BILL_DATE$$’, ‘ { SY-DATUM } ‘ )) |

*            && |  WHERE mandt = { sy-mandt }    GROUP BY mandt, pernr, bill_rate, bill_date |.

*     Create an SQL statement to be executed via default secondary DB connection

      CREATE OBJECT lo_sql_stmt EXPORTING con_ref = cl_sql_connection=>get_connection( ).

*     execute the native SQL query/ SQL Call

      lo_result = NEW cl_sql_statement( )->execute_query( lv_sql ).   ” new syntax

*     read the result into the internal table lt_partner

*      GET REFERENCE OF lt_PROJ INTO lr_data.

      GET REFERENCE OF lt_proj INTO lr_data.

lo_result->set_param_table( lr_data ).  “Retrieve result of native SQL call

      lo_result->next_package( ).

      lo_result->close( ).

    CATCH cx_sql_exception INTO lx_sql_exc.

      lv_text = lx_sql_exc->get_text( ).

      MESSAGE lv_text TYPE ‘E’.

  ENDTRY.

    LOOP AT lt_PROJ ASSIGNING FIELD-SYMBOL(<fs_PROJ>).

    WRITE: / ‘Employee Proj Info’ ,<fs_proj>-pernr.

    WRITE: ‘=’, <fs_proj>-proj_name , /.

  ENDLOOP.

Output: