Problems in SQL transform with variable in statement

[Former Member]

Hi,

I have an sql transform with a variable in the statement. It looks like this:

SELECT * FROM I.X where I.X.CUSTOMERNUMBER IN ([$variable])

I have a script running prior which sets the $variable = '\'366800\',\'000933\'';

(the CUSTOMERNUMBER field is a string)

The problem is when DI compiles the SQL with the variable value, it is replacing each single quote with two single quotes, and thus turning my list of values into a single string (as recognised by DB2).

How can I work around this? The CUSTOMERNUMBER field is indexed and the table is very large, so I want to use a very efficient statement to retrieve the data. I'm inserting this data into a different database, so I can't use the sql() function to execute the query and insert the rows into a new table in the same database (don't have authority to do that).

I also need the sql to be dynamic because the customer numbers required will change from time to time. They're listed in another database and I plan on writing a loop to set the $variable value to contain all values in the lookup table, but I can't use that in a join because its in a different database and would not be efficient.

Does anyone have any ideas? I'm literally all out, have tried a wide range of things to no avail, and none of my colleagues can work it out either.

Thanks very much,

-Steve

[Former Member]

Greetings Thread Originator,

This post is older than 60 days and there are no entries in the past 30 days. Based on the content discussed, it appears that you question has been

answered. This message is being marked as answered and points are being assigned if available where possible.

Thank you for being an active participant in the SAP Forums,

Rob Siegele

Forum Moderator

SAP Americas

[werner_daehn]

I assume the SQL Transform is used only because of that in-list, not for other reasons....

In this book page here: https://boc.sdn.sap.com/node/20046 at the very bottom it isdescribed on how to use the pushdown_sql() function in a where clause to add *any* text of your choice into the select statement DI generates. In this example I used a "where exists" but you can easily change the text to

where key=5 and pushdown_sql('my_datastore', 'CUSTOMERNUMBER IN ([$variable])') and gender='M'

I used the key=5 and gender=M just to clarify the syntax (I hope).

For the SQL Transform, I wonder if you need the [$variable] syntax as well....

[Former Member]

Hi,

Thanks for your reply. I tried using the pushdown_sql function in the where clause of a query. But I can't get a variable to be the parameter, or part of the parameter. It doesn't recognise the variable, instead it treats it as literal text.

Any further thoughts?

[Former Member]

Isn't this simply a syntax error ? I would expect to see square brackets [ ] around the variable, the square brackets indicate that what's inside needs to get substituted with the result of the expression (in this case a variable) :

SELECT * FROM I.X where I.X.CUSTOMERNUMBER IN ( [$variable ] )

[werner_daehn]

This might help as well: https://wiki.sdn.sap.com/wiki/pages/viewpage.action?pageId=49414207

[Former Member]

I don't think pushdown_sql will work with SQL transform, you may have to replace with table or view, if you can't avoid using SQL transform and still want to use the IN CLAUSE using variable then

you can try initialising the $varaible using multiple varaibles, as below

$var1 = 'A';

$var2 = 'B';

$variable = '{$var1},{$var2}';