Consuming HANA Views, Procedures, External Views in ABAP 7.40 Syntax – Part 2
- ABAP Report with new data declaration syntaxes on 7.40
- ABAP Report on HANA using ADBC
- 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.
SELECT *
INTO TABLE lt_tab
FROM 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:
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
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
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
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
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
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
It seems we can't consume calculation view which has input parameters in ABAP via External view, because ABAP doesn't support PLACEHOLDER.
Right?
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
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?
Hello,
Question : How stable is this approach/solution when dealing with high data volume?
Question: if a HANA view is moved to a different package, how am I able to change the package namen in the external view? the refactor process does not take care....
Nice blog.. Thanks Shahid. 🙂
Nice blog.
I have a question here.
As we can see in your screen shot, All the fields are marked as “Key fields”.
Same situation in my case as well. I marked few columns as Key in my calculation view but it did not work.
I would like to know if anyone finds a way to mark them as Key and Non-Key fields.
All fields are keys
Best regards,
Ramjee Korada
Hi,
Nice blog, but how can we have the field descriptions in the Dictionary View based on an external view?
Thanks