HANA stored procedure with IN param as table

[shishupalreddy]

Hello HANA experts ,

We have the following scenario ...

We would get the result from Analytic view using ADBC  from HANA database .

Now we need to push query to HANA database to get the data based on the above data (ofcourse from Analytic view which is different from above) .

Here we are thinking to have procedure and inside procedure firing SQL query to get the data form second analytic view ...

In this case please advise how to send INPUT PARAMETER to Procedure as TABLE parameter and inside the procedure how to make use of the input table (like FOR ALL ENTRIES  in ABAP .) to get the data .

Thanks

[jitin_kharbanda]

Hello Shishupal,

I also need to pass the internal table as input parameter to call HANA Procedure from ABAP.

If you had resolved this, kindly let me know the solution you implemented.

Thanks

[shishupalreddy]

Hello Jitin,

In SPS6 we have concept of Dynamic SQL queries , I think we could use APPLY_FILTER statement which is available from SPS6 version . I have not used . you can give  a try .

Thanks

[lbreddemann]

Hi there,

not sure that I really got your requirement here... but maybe you'd be better of by using a calculation view in the first place.

Also, please describe your actual business requirement. Would be interesting to hear about this.

- Lars

[shishupalreddy]

Hello Lars ,

My requirement is as follows.

- I have multiple selection parameters in R/3 , which I would be passing to attribute views and get the output using Procedures in HANA .I would be passing all selections via Input parameters with procedure.

- Now The output of the above would be table of values , which I need to pass to another view to get the final result again as table of values into R/3 .Can you please how do I pass the table of values coming from the step one into second view within the same procedure .

For example : Input ---->VIEW1 --> OUTPUT(Table of values)

                    OUTPUT------>VIEW2--->RESULT. This step I am not clear how to proceed .

Idea is to avoid multiple data transfers from R/3 to HANA , HANA to R/3 .Once I pass input I should get final output from HANA to R/3 .

Hope you understood my requirement .Please advise.

Thanks

Shishupal.

[iwan_santoso4]

Last time I checked, ADBC does not support input parameter as internal table. I asked the same question after SAP TechEd 2012. But I don't know the current stage.

If you use SAP Netweaver 7.4, you should have a better flexibility since you can access the view from ABAP directly i.e. no ADBC required. I assume you are using ADBC because you are not using 7.4 yet.

[shishupalreddy]

Hi

Yes ,we are not yet into 7.4 , where i can use Data base procedures .

By the way I have another issue with calculation views ,

I am trying to read Calculation view data using SQL statement , but when I have SQL for ex as below , it says invalid col name , though the col is present in the view output .

select * from <calc view> where _ABS_TEST123_From = <some value>  ---Error message saying

_ABS_TEST123_From is not a valid col.

but when i have

select * from <calc view> where _ABS_TEST345 = <some value> , it works fine .

Please advise .

Thanks

[former_member184768]

Hi,

Ideally you should have opened a different thread for this issue, but can you please post the screenshot of the query and columns in the model. There could be some formatting issue, which happens in most of the cases. Something like enclosing the column names in double quotes and all.

The screenshots will help understand the issue in a better way.

Regards,

Ravi

[shishupalreddy]

Hello,

I have opened a new discussion thread for SQL issue with calculation view .

But would keep this thread open to see if  i can get any idea on the original content of this thread .

Thanks

Shishupal

[yeushengteo]

Hi Shishupal,

Maybe you have to consider to use intermediate tables for your requirement.

One to store the parameters selection and one to pass the table record back to ABAP. You call the precedure WITH OVERVIEW and query the record in the intermediate table subsequently.

Regards.

Yeu Sheng

[shishupalreddy]

Hi Yeu Sheng,

Thank you for the response.

Some what I understood about the proposed solution .Could you please explain with an example how to achieve this using ADBC classes of ABAP .

With Best Regards,

Shishupal.

[yeushengteo]

Hi Shishupal,

Please see this thread first http://scn.sap.com/thread/2147729 for calling procedure "WITH OVERVIEW" syntax.

I am assuming through ADBC calling, you needs something as follows:

* Empty the parameter content table

lo_sql->execute_ddl( 'TRUNCATE TABLE "<<parameter_temp_table1>>"' ).

*Populate the parameter content table

LOOP AT <<myparameters>> INTO <<ws_parameters>>.
    lo_sql->execute_update( 'INSERT INTO "<<temp_table1>>" VALUES (''' && <<ws_parameters>> &&''' )' ).
ENDLOOP.

* Empty the result table

lo_sql->execute_ddl( 'TRUNCATE TABLE "temp_table2"' ).

* Call my procedure to populate the result table

lo_result = lo_sql->execute_queryhe ( 'CALL "_SYS_BIC"."<<myprocedure1>>"( "<<temp_table1>>", "<<temp_table2>>" ) WITH OVERVIEW ' ).

* Close DB connection    
lo_result->close( ).

* Get result table into internal table

lo_result = lo_sql->execute_query( 'SELECT * FROM "<<temp_table2>>"' ).

You can pass the parameter value into a temp_table1 (IN) first for handling within your procedure or you can use the temptable1 directly in modeling. Your program call the myprocedure1 for DB processing. Within your procedure logic, you can also call to other other procedure. Finally you pass back the result in the temp_table2 (OUT) and issue a select statement from the temp_table2 directly to get the result.

This is what i imagine in using intermediate tables to retrieve table content from procedure.

Regards..

YS

[shishupalreddy]

Hi Yeu Sheng,

Thank you for your response . I will tryout this and  get back to you ..

Regards,

Shishupal

[shishupalreddy]

Hello ,

I tried but not succeeded. Please see the requirement below .

I have one Attr view from which I need to select some col s .

and i have a second view from which i need to select all cols based on the values from the first view .

The selection condition for the first view comes from ECC for example

out = select col from <view name > where {condition} . Here condition must be input var which comes from ECC with the conditions .

Now from second view for instance

result = select col1 col2 col3 from <result view > where col1 -> all values from first view .

Please advise how to get this done .Now we are hitting HANA from ECC twice (first time for selection and then second time hitting result view to get the final result of the search ) .This approach does not work incase the result from 1st view has more than around 25k entries .

Please advise .

Thanks

Shishupal.

[shishupalreddy]

Hello ,

My procedure is as follows .

BEGIN

   --IF :CONDITION IS NOT NULL

    FLYERDATA = SELECT DISTINCT FLYERID FROM

    "_SYS_BIC"."d-rbw-mat-mds/D_SEARCH_SELECTION_VIEW"

    where --:CONDITION

    WITH PARAMTERES  ( 'PLACEHOLDER' = ('$$condition$$'));

     --           ENDIF;

                --( :CONDITION

END;

The below is the error on validation

Repository: Encountered an error in repository runtime extension;error preparing statement create procedure "_SYS_BIC"."d-rbw-mat-mds/DT3_SEARCH_SELECECTION" (  in CONDITION VARCHAR(4999) ,  out FLYERDATA "_SYS_BIC"."d-rbw-mat-mds/DT3_SEARCH_SELECECTION/tabletype/FLYERDATA"  )

language SQLSCRIPT sql security definer reads sql data as n

/********* Begin Procedure Script ************/ n BEGIN n

  --IF :CONDITION IS NOT NULLn 

  FLYERDATA = SELECT DISTINCT FLYERID FROM n    "_SYS_BIC"."d-rbw-mat-mds/D_SEARCH_SELECTION_VIEW" n  

  where --:CONDITIONn 

  WITH PARAMTERES  ( 'PLACEHOLDER' = ('$$condition$$')); n     --  ENDIF;n  

  --( :CONDITIONn END; n /********* End Procedure Script ************/

  for oid {tenant: , package: d-rbw-mat-mds, name: DT3_SEARCH_SELECECTION, type: 2}:

  sql syntax error: incorrect syntax near "CONDITION": line 1 col 74 (at pos 74) at qp_gram.y:29622Repository:

  Encountered an error in repository runtime extension;error preparing statement

  create procedure "_SYS_BIC"."d-rbw-mat-mds/DT3_SEARCH_SELECECTION" (  in CONDITION VARCHAR(4999) ,  out FLYERDATA "_SYS_BIC"."d-rbw-mat-mds/DT3_SEARCH_SELECECTION/tabletype/FLYERDATA"  ) language SQLSCRIPT sql security definer reads sql data as n /********* Begin Procedure Script ************/ n BEGIN n   --IF :CONDITION IS NOT NULLn    FLYERDATA = SELECT DISTINCT FLYERID FROM n    "_SYS_BIC"."d-rbw-mat-mds/D_SEARCH_SELECTION_VIEW" n    where --:CONDITIONn    WITH PARAMTERES  ( 'PLACEHOLDER' = ('$$condition$$')); n     --           ENDIF;n                --( :CONDITIONn END; n /********* End Procedure Script ************/ for oid {tenant: , package: d-rbw-mat-mds, name: DT3_SEARCH_SELECECTION, type: 2}: sql syntax error: incorrect syntax near "CONDITION": line 1 col 74 (at pos 74) at qp_gram.y:29622                                                                                                                                                                 

Please advise .

Thanks

[yeushengteo]

Hi,

My idea is the result column from first view is used to insert into a temp table where it is used for joining to the 2nd view. The 2nd view will only returns records when there is a valid join by the temp table record upon a SELECT on the 2nd view. Hence you always need to truncate the temp table first before inserting new selection.

On the other hand, will the INSERT operation slow down the performance, you test and feedback here later. Maybe what you doing presently by a 2 DB calls maybe better efficient too

Regards.

YS

[AndyAnand]

did you miss typing the where clause in the above post ?