topic List within a variable to use in SQL select in Connectivity & Data Prep

List within a variable to use in SQL select

DirkCtz — Tue, 16 Nov 2021 20:19:50 GMT

Hi

I have a list of customer numbers I write to a list and then store it in a variable, ie

vList = 12345, 875895, 98371.... (less than 1000 customer numbers in the list).

I then try to use the variable in a SQL script in the loader, something like:

SQL Select * from Table where Customer_Num in ($(vList));

But I get the following error: Syntax error: expected something between '(' and ')'.

If I type the Customer numbers into the SQL in the following format manually, i.e. '12345, 875895, 98371' - - it works. But when I try doing it in the variable, it doesnt work and gives above error.

I also tried char(39) & vList & char(39)... no luck

Any advice?

Re: List within a variable to use in SQL select

maxgro — Tue, 16 Nov 2021 22:00:05 GMT

SET vList = 12345, 875895, 98371 ;

// try this

SQL Select * from Table where Customer_Num in ('$(vList)');

// or this
SQL Select * from Table where Customer_Num in ($(vList));

Re: List within a variable to use in SQL select

DirkCtz — Wed, 17 Nov 2021 21:50:13 GMT

Thank you..

Unfortunately neither of these options work 😞

The within the variable I store a concatenated list. If I type in the
customer numbers in the variable it does work, but if i assign the
concatenated list to the variable it doesn’t work.