Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Problems in SQL transform with variable in statement

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
Not what you were looking for? View more on this topic or Ask a question