Optimized sql not properly generated when using SAP tables

[Former Member]

Hi Experts

We are using BODS 4.1 sp2.

We have a simple dataflow where we pull data from SAP r3 using direct download and push it in our database.

Basically its like this:

SAP Table-->Query Transform-->Oracle Database

Inside query transform,we have applied some conditions in where clause,which are combinations of 'or' and 'and' operator.

However when we are generating optimized SQL for the same,we observe that the conditions in where clause are not being included in the optimized query.

But if we replace all conditions by only 'or'  or by only 'and' condition,the optimized sql query with condition  is generated.

Somehow the optimized sql with conditions is not generated if where clause has mixer of 'and' and 'or' conditions.

The issue is only observerd for SAP Tables

However the same dataflow in bods 4.0,generates an optimized sql containing the mixed conditions of 'and' and 'or' in where clause.

Please help me on this.

[Former Member]

Dear Shiva

We e

Let me explain this in detail to you:

We have a simple dataflow where we push data from the sap table directly into our oracle database.

The dataflow has a query transform where we have some conditions to filter the data.

The method used to pull data from R3 is direct download.

System Used:

BODS 4.2 SP1

SAP R3 620 release(4.7)

The where clause has mixed 'and' and 'or' operators.The problem is that this where clause is not getting pushed in the Optimized sql.

Eg: Table1.feild1=some value and Table1.feild2=some value or Table1.feild3=some value.

If this is the condition in  where clause,the where clause does not appear in optimized sql.

Also the condition in where clause is from same table.

However if all the condition in where clause are either all 'and'  or either all 'or',the condition gets pushed and appears in optimized sql.

But if their is mixed 'and' and 'or' it fails.

The same was not the case when we used BODS 4.0 with R3 620(4.7).

We investigated further and used R3 730 with BODS 4.2.With R3 720,we the where condition was getting pushed into optimized sql for mixed 'and' 'or' predicated.

SAP support too confirmed the same.They were able to reproduce the issue.

We will use ABAP dataflow and see if if resolves the issue.

Regards

Ankit

[Former Member]

Hi Experts

After digging more into this issue,we found a very strange issue.

-->When we attempt to connect our BODS 4.2 TO SAP  R3(620 release),we are unable to generate correct optimized sql.

-->However when we connect our BODS 4.2 TO SAP  R3(730 release),we are able to generate correct optimized sql.

Is anyone facing this starnge issue with??

Please assist.

[former_member200473]

Hi Ankit,

One small question :

As per the statement "optimized sql is not working properly" does it mean you are not able to see the push down or it is not showing any sql.

As per my understanding pushdown sql in optimized sql will only be generated when you have same source and target datastore otherwise it will generate the simple sql.

could you please be more clear in this thread .

Shiva Sahu

[Former Member]

Hi ppl

A mistake in the above post

We are using BODS 4.2 SP1 ,IPS 4.2 sp2.

Sorry for the mistake.

[former_member231565]

I Believe when ever you are extracting data from SAP R3 tables. i think its recomended to use ABAP dataflow..Please Correct Me if  i am any thing wrong

Regards,

R.Sumanth.

[Former Member]

Yes,i know its is recommended to use ABAP dataflow to pull data from R3.

But as stated earlier we did not face such issue in previous versions.