Create a string variable that can be used in WHERE clause from other tables

[kevin_creese]

Hello Community!

This is probably an easy one for this group, but I'm struggling.

I have a list of items that I have assign to a variable in my load script.  My plan is to use this list of items in the WHERE clause of other tables to limit the data that I'm returning so that I only return the items assigned to my variable.

Here is how I'm building my variable string:

Load holiday list into variable in script

// Create a temporary table

tmp:

NoConcatenate

Load concat(Distinct item_no,',') as FieldA

Resident <TABLE>;

let vSKU = peek('FieldA',0,'tmp');

drop table tmp;

This is yielding values like this: vSKU = ABCD-123,ABCF-456,ABFG-321

When I load my next table and try to use a WHERE clause that performs a MATCH(item_no_2,'$(vSKU)') > 0 I'm getting no results.

Based upon my testing, I know that I need to wrap my item numbers with a single quote (') so that they look like this:

    vSKU =  'ABCD-123','ABCF-456','ABFG-321'

What function and I missing or misusing that will help me do this?

[sunny_talwar · Accepted solution]

Try this

// Create a temporary table

tmp:

NoConcatenate

Load Concat(DISTINCT Chr(39) & item_no & Chr(39),',') as FieldA

Resident <TABLE>;

LET vSKU = peek('FieldA',0,'tmp');

DROP Table tmp;

[vishsaggi]

May be try below?

tmp:

NoConcatenate

Load concat(Distinct  Chr(39)&item_no&Chr(39),',') as FieldA

Resident <TABLE>;

let vSKU = peek('FieldA',0,'tmp');

drop table tmp;

Then try like without Quotes.

WHERE Match(item_no_2, $(vSKU)) > 0 ;

[kevin_creese]

Perfect!  I had to modify my MATCH to MATCH(item_no_2,$(vSKU)) > 0 and then the stars aligned and I can get on with this project!  Thanks Sunny!

[sunny_talwar]

Super awesome!!