How to call a Hana Calculation View from HDB Procedure

[Former Member]

Hi,

   I am implementing a hdb procedure which internally calls a Calculation view by passing a set of parameters.

One of these parameters is a variable .. defined in the procedure itself.

Can someone guide me on how I can call a calculation view (by passing variable parameters) from a hdb procedure.

Any help is highly appreciated.

Thank You.

Alekhya

[former_member194780 · Accepted solution]

Hi Alekhya,

For calling a CV with input parameters / variables in your procedure try:

SELECT <field1>,<field2>,... FROM "_SYS_BIC"."CV_EMP" ('PLACEHOLDER' = ('$$IP_EMP_ID$$', '1')) WHERE (("STATUS" IN ('1') ))

$$IP_EMP_ID$$ --> Input parameter

WHERE (("STATUS" IN ('1') )) --> Variable that is set in the Calc View

-Avinash

[Former Member · Accepted solution]

Hi Avinash,

    Thank you for the quick response, but it did not work.

ERROR:

Syntax error in procedure object: incorrect syntax near ":basecasekey": line

where basecasekey is my variable.

EDIT:

Here is my query,

SELECT distinct "CASE_KEY" FROM "_SYS_BIC"."Deloitte.Innovation.saint.models/CASELISTFILTERS"

    ('PLACEHOLDER' = ('$$EVT_SERIOUS_IND$$', 'null'),

    'PLACEHOLDER' = ('$$EVT_RELATED_IND$$', 'null'), 'PLACEHOLDER' = ('$$FILTERTYPE$$', 'S'),

     'PLACEHOLDER' = ('$$BASECASEKEY$$', :basecasekey));

EVT_SERIOUS_IND,EVT_RELATED_IND, FILTERTYPE and BASECASEKEY are the column/filed names in my Calculation View.

'basecasekey' is the variable defined in my procedure.

Error:

Syntax error in procedure object: incorrect syntax near ":basecasekey": line

Regards.

[pfefferf · Accepted solution]

Hello Alekhya,

the syntax in SQLScript is a little bit different than described above (which is for SQL queries).

Example

...

BEGIN

DECLARE lv_variable nvarchar(10) := 'dummy';

  lt_data = SELECT * FROM calculation_view ( PLACEHOLDER."$$PARAMETER_NAME$$ => :lv_variable );

END;

You have to address your parameters with PLACEHOLDER."$$<your parameter name$$".

So your query would look like following:

SELECT distinct "CASE_KEY" FROM "_SYS_BIC"."Deloitte.Innovation.saint.models/CASELISTFILTERS"

(

   PLACEHOLDER."$$EVT_SERIOUS_IND$$" => NULL,

   PLACEHOLDER."$$EVT_RELATED_IND$$" => NULL,

   PLACEHOLDER."$$FILTERTYPE$$" => 'S',

   PLACEHOLDER."$$BASECASEKEY$$ => :basecasekey

)

Best Regards,

Florian

[former_member194780 · Accepted solution]

For input Parameters use :

(PLACEHOLDER."$$BASECASEKEY$$" => :basecasekey)

Variable are executed using the WHERE clause...

eg. WHERE (("VARIABLE_NAME" IN ('123')));

-Avinash

[Former Member · Accepted solution]

Hi Florian,

     Thank You very much.

This is something which I've learnt today

Regards,

Alekhya

[jyoti_senapati]

Hi Alekhya,

The best way to use data of calculation view in stored procedure is store the data in a table variable and retrieve required data from that table variable to do calculation or reports.

No need to declare table variable in declare statement. You can directly write the statement as per below syntax.

Temp_Calc_View_data = Select <field_names> from

                                        "_SYS_BIC"."<Calc_view_name>" where <filed_name> = <filter_name>

now you can retrieve data from table variable Temp_Calc_View_Data like:-

Select <field_names> from :Temp_cal_view_data.

By using this approach you procedure will consume lesser time to execute and you will not face any issue.

For the syntax error as you have mentioned above just check ':', or ' "" ' in your select statement. 

[Former Member]

Hi Jyoti,

My doubt is about passing variable parameters to a calculation view from a hdb procedure.

1. As much as I know, you can't pass parameters to a Calculation view using "WHERE" clause.

2. " " (double quote) in Hana is used only for column names.

Thank You

[jyoti_senapati]

Hi Alekhya,

1.Yes you can pass hdb stored procedures input parameters as where statement in calculation view.

2.It is own choice to use ""(double quote) in column names.If you will not use then there will be no issue.

Replay back with your code where are you facing issue while using calc view in SP.

Regards,

Jyoti