Skip to Content
0

Dynamic WHERE Clause in SAP HANA Calculated view

Jan 13, 2017 at 05:38 PM

684

avatar image
Former Member

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?

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Florian Pfeffer
Jan 16, 2017 at 11:53 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Raj K
Jan 16, 2017 at 05:46 AM
-1

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;

Share
10 |10000 characters needed characters left characters exceeded