WHERE parameter on virtual table

[SvenBoetius]

Hi,

I am running into problems while executing an AMDP method on virtual tables.

We have tables in a remote Oracle database. The tables are added as virtual tables in HANA and are working fine. A SQL-statement like the following returns the requested data.

tab = SELECT one, two, three      
      FROM "abc"."vt_oracle"
      WHERE one = 'xxx'

But selecting the whole table takes too long, as there will be 20 other systems which have to be queried at the same time and every table has up to 250million entries. We only need a subset of the data, so I added the method parameters x and y to the funtion to use in another WHERE clause and query the tables in smaler parts.

tab = SELECT one, two, three      
      FROM "abc"."vt_oracle"
      WHERE one = 'xxx'
        AND two BETWEEN x AND y

While in the first select statement the WHERE one = 'xxx' clause gets pushed down to the Oracle-database in the second both parts of the WHERE-clause are not pushed down and the query takes MUCH more time to execute.

It seems like parameters can't be pushed down to the remote source. Is there a way for me to work around this?

[matt]

Run a trace on the remote source and see exactly what the SQL issued there was. At the moment you're guessing the where clause isn't being passed to the remote system, but it might be something else.

[SvenBoetius]

I can see the executed Statement via Smart Data Access. Thats why I am sure, that WHERE is not passed.

[DoanManhQuynh]

so if its not parameters but fixxed value, would it be pushed to oracle? could you try it.

[SvenBoetius]

A fixed value gets pushed down to db.

[srinivasareddym]

Hello Sven,

Did you find the workaround on this, if yes can you please provide solution?

https://answers.sap.com/questions/13898178/sap-sdi-filter-push-down-from-virtual-table-to-ora.html

Thank you.

[matt]

Try using cl_sql_statement constructions instead. It doesn't explain why your AMDP doesn't work, but it is certainly a more powerful/flexible/controlable option.

[p244500]

HI,

check below link

https://help.sap.com/doc/abapdocu_752_index_htm/7.52/de-DE/abenamdp_hdb_sqlscript.htm

Regards,

Nawa

[matt]

Couldn't even check that it was in English.

[former_member184158]

Do u think your link is helpful and you know in which language is?