Skip to Content

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 :


/wp-content/uploads/2014/01/1_358704.jpg

/wp-content/uploads/2014/01/2_358705.jpg



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.

/wp-content/uploads/2014/01/3_358715.jpg

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

/wp-content/uploads/2014/01/4_jpg_358716.png

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

/wp-content/uploads/2014/01/5_358717.jpg

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

/wp-content/uploads/2014/01/6_358718.jpg

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

/wp-content/uploads/2014/01/7_358719.jpg/wp-content/uploads/2014/01/8_358720.jpg

Source Code to consume the above created external View:


* External View
DATAlt_tab TYPE TABLE OF external_view.

SELECT *
INTO TABLE lt_tab
FROM external_view.

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


Output:


/wp-content/uploads/2014/01/9_358721.jpg


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.

/wp-content/uploads/2014/01/10_358722.jpg

T5. Consuming Analytic View/Calculation View in ABAP


Calculation view can also be consumed in the same way.


Step 1:  Create HANA Analytic view

/wp-content/uploads/2014/01/11_358725.jpg


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:

/wp-content/uploads/2014/01/12_358726.jpg

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Kris Claes

    Hi,

    interesting and confirms what we are doing.

    But I have one question: we cannot create these external views. The ‘external view’ button is not highlighted in the creation screen.

    Is this due to the fact that we are working with HANA in a side-car scenario? HANA is our secondary database?

    And if so: are there any ‘tricks’ to generate the corresponding views in ABAP? download something and generate by something else in ABAP?

    Regards.

    Kris

    (0) 
    1. Jasmin Gruschke

      Hi Kris,
      unfortunately you can only create the external view it the HANA is your primary DB (of course leaving out programatical creation, which is however quite nasty for lifecycle as you might guess…).

      Cheers,

        Jasmin

      (0) 
      1. Kris Claes

        Thanks for this answer, but recently we are implementing changes for FI with HANA.

        And one of the first things I find are three external views in package GLT0 V_GLPOS_C_CT, V_GLPOS_N_CT and V_GLPCA_CT.

        They correspond to Column views in HANA.

        How can this be explained?

        Regards.

        Kris

        (0) 
        1. Jasmin Gruschke

          Hi Kris,
          you refer to delivered SAP content. So the colleagues in the development system have HANA as primary DB and were therefore able to create external views.

          The question in your case is, whether these views can be consumed. This means, the External View (as a proxy object) exists and is active, however, this is certainly not the case for the underlying column view on your primary DB (which is not a HANA as I understood) and the situation is certainly the same in your “side-car HANA”.

          You can try to query on the external view, e.g. via OpenSQL using the connection to the secondary DB. This certainly will lead to a runtime exception because the HANA column view (you can find the name in the external view) corresponding to the proxy object is not available.

          You might be lucky, if the “side car” is in fact an ABAP on HANA system. Assume, e.g. that you have an ABAP system on a non-HANA DB as your primary system and another system with a HANA underneath with the same ABAP version on top. If you now access the HANA via the secondary DB connection, you can query on the external view, respectively the column view. The reason for this behaviour is, that the activation of the external view (in the ABAP on HANA stack) lead to the activation/deployment of the HANA view.

          Cheers,

            Jasmin

          (0) 
          1. Kris Claes

            Strange, because we can easily consume it.

            As far as I know this is all implemented with notes, and we have only one system (with Oracle as primary and HANA as secondary database) …

            Greetings.

            Kris

            (0) 
            1. Jasmin Gruschke

              Hi Kris,
              not to strange for me. You have the active external view, which knows to which HANA view it’s related. So, if you query via secondary DB connection, the related HANA view is queried and the data dictionary has the type mapping information, so OpenSQL will happily return the result set.
              Yet, what is not possible is to create (via the ABAP development tools) a proxy object for an existing HANA view in the secondary DB, since the DDIC will only have a look at the primary DB when creating proxy objects.

              Cheers,

                Jasmin

              (0) 
  2. Guoliang Zhou

    It seems we can’t consume calculation view which has input parameters in ABAP via External view, because ABAP doesn’t support PLACEHOLDER.

    Right?

    (0) 
    1. Jasmin Gruschke

      Hi,
      yes that’s correct. You can only consume HANA views without input parameters or those having “default” values attached. If you need to pass input parameters, you may use a wrapper view or a wrapper database procedure. Via the DB procedure proxies or via an ABAP managed DB procedure you can pass the necessary input parameters.

      Cheers,

        Jasmin

      (0) 
  3. Yang Yang

    Hi,

    There  is an error “no Dictionary type exist for database type VARCHAR” when I want to create the external view.Is that means the external view can’t support varchar?

    (0) 

Leave a Reply