Dynamic WHERE Clause in SAP HANA Calculated view

[adithya_kambmpati]

Hello Everyone,

I am trying to create a simple scripted calculated view which does the following:

1. Created an Input parameter for accepting the WHERE Clause for the SQL query

2. Get the count of records from a big view/table based on the input query. I am trying the following options, but none of them work.

var_out = SELECT COUNT(*) AS REC_COUNT FROM ZTEST_TABLE where (:lv_whereclause);

or

var_out = SELECT COUNT(*) AS REC_COUNT FROM (:lv_whereclause);

or

lt_out = SELECT COUNT(*) AS REC_COUNT FROM ZTEST_TABLE

var_out = apply_filter(:lt_out,:lv_whereclause);

Can any one help me to resolve the issue?

[pfefferf · Accepted solution]

Dynamic SQL in the way you are trying to do it, is not supported in SQLScript. For dynamic SQL the complete SQL Statement has to be executed with EXEC or EXECUTE IMMEDIATE. But both statements are not supported in read-only objects like Scripted Calculation Views.

For your case you have to use APPLY_FILTER, but in a way that the filter is applied to the table content and not only to the "count(*)" result you created. As you are also not showing what content your "lv_whereclause" variable has, no complete valid example can be given for your case.

An example (with a "dummy" where clause - knowing that it does not fit for column names(s)):

--lv_whereclause = 'TEST_COLUMN = ''filter value''';

lt_result = APPLY_FILTER(ZTEST_TABLE, :lv_whereclause);

var_out = SELECT count(*) AS rec_count from :lt_result;

Consider also to add the schema name in front of the table if it is necessary to address the table.

The online documentation for APPLY_FILTER contains also examples on how to create the filter.

Regards,
Florian

PS: I would also recommend to use a table function as datasource in a graphical calculation view instead of a scripted calculation view. That will save you time and costs for migration in future.

[rindia]

Hi Adithya,

You are missing the column name of table to be used for filtering. Try like this:

var_out = SELECT COUNT(*) AS REC_COUNT FROM ZTEST_TABLE where column_name = :lv_whereclause;