Planviz removes where clause - Virtual Table on remote table in MS SQL DB

[ravi_rawat]

Hi Experts,

I am facing strange issue. My calculation view X is based on virtual table & reads from remote table Y in MS SQL Database.

Remote Table Y has fields: F1, F2, F3, F4

Calculation View X has fields: F1,F2,F3,C4

Difference between X & Y is, X does not have field F4 but has calculated column C4 based on F4.

I am running below SQL Statements A & B .Check the remote scan seen in Planviz in both cases.

SQL A:

SELECT * FROM X where F3 = 'Z123'

Remote Scan SQL = SELECT * FROM Y where F3 = 'Z123'

SQL B:

SELECT * FROM X where C4 = 'Z456'

Remote Scan SQL = SELECT * FROM Y

One can observe, in SQL B, where clause is dropped. This is causing us to fetch entire data from source which is time consuming.

Has anyone faced this scenario and how it was addressed. Looks like HANA engine is not liking where clause on calculated columns.

Thanks,

Ravi

[Cocquerel · Accepted solution]

Hi,
I think the Engine does not know how to calculate F4 based on C4 value and so, it is not able to generate the WHERE clause for the remote SQL.
First solution I see would be to:

- create an input parameter for your view to provide C4 filter value.
- create a filter in your projection node for table Y based on input parameter value with the correct formula.

An other alternative would be to create a view on your remote MS SQL system that would perform C4 calculation. Then, your virtual table would be on top of this remote view. This way, the C4 WHERE clause will be propagated to the remote system.

Regards,
Michael

[ravi_rawat · Accepted solution]

Hi Michael,

Your suggestions really helped me. I was able to pass the parameters in MS SQL. Only problem is now, when I am trying to handle situation when no parameters are passed. In that scenario, I would like to get entire data. This situation can be handled using Match() function in filter expression.

For example:

MATCH("F1", if('$$IP_F1$$' = '','*','$$IP_F1$$'))

Using this again creates the same issue. In Remote Scan SQL, where clause is dropped for Column - F1

Is there any alternative way?

Thanks,

Ravi

[Cocquerel · Accepted solution]

raviprawat2 did you try to set default value * for your input parameter instead of using this if formula ?

[ravi_rawat · Accepted solution]

Hey Michael,

* does not work. As a work around I used range to cover all scenarios.

Your suggestions really helped me to build my solutions. Using Input Parameters, Stored procedures and range addressed my problem.

Thanks,

Ravi

[DotEiserman]

Hi Ravi,

Can you share with us the calculation of column C4 based on F4? Because you're using F4 in a calculation, the engine will first fetch all the data, do the calculation inside the calculation view, and then apply your where clause to the calculated column, because this column only exists in the calculation view it is not able to push the logic down to source, hope that makes sense, C4 doesn't exist on source so it can't push the logic down.

Thanks

Dot