Variables of SQL Script Calculation View

[Former Member]

Hi Guru:

I have a calculation view with SQL script. Due to the volume of the underlying table, I need to filter the SELECT statement to make the view faster.

My first look is to use the variable. However, I find out the variables is invisible in the script ( am I right? as I have no option to create a variable in script, I can only create it in the output, then how to use it in the script?).

Then I look at parameter. But it is not useful for me as I need a multiple single value selection.

So is there anyway to pass the user selection to the SQL script calculation view?

Thanks.

Eric

[former_member184768]

Hi Eric,

To my understanding, HANA pushes down all the variables / filters to the database irrespective of the fact that if they are defined in the View definition or in the front end tool.

Hence I'd suggest you to define the variables in the front end tool as multiple single value. HANA will push the filters down to the database level and it may not have much impact on the performance.

This needs to be confirmed by somebody from SAP, but in my experience there was not much performance impact.

Regards,

Ravi

[Former Member]

Thanks for the reply.

But I guess it could not push down for some complicated logic.

An easy example, my script will convert the value 'A' from the DB to 'A1' in the view, 'B' from DB to 'B2' in the view, etc. If I define a variable at the frontend with value B2, will HANA be able to translate it to 'B'?

It is just a simple example, considering some attributes may be combined from multiple fields, it would be impossible to push it down.

[Former Member]

Hi Eric,

I have 2 questions:

1. Are you using t-SQL or CE Functions?

2. Are you using any Sub Queries in your script?

If there are no sub queries in your script then you can create variables in output and it will work just like your where clause condition. On top of this, you can create  mandatory prompts in your front end tool to pass the user's selection to these variables.

But if you have sub queries in your script, and you want to pass the values selected by user in frontend, to the sub query, then apart from input parameter, there is no other way.

So to conclude, if you have to pass multiple values for a field to a sub query from front end, then i do not think that it is possible in HANA.

Regards,

Piyush