Multiple values from variable in where condition in SQL query

[achakilam1022]

Hi all,

I have a variable vField . Its value is  'a','b','c','d'

How do I write a SQL query to check where field matches any values (a or b or c or d) in vField variable?

Thanks!

LIB CONNECT TO database1;

Table1:

Load Field,

1 as PlaceHolder;

select distinct Field from db1.xyz

where ....

;

Table2:

load

Concat(chr(39)&Field &chr(39),',') as Field_new

Resident Table1

Group by PlaceHolder;

Drop Table Table1;

let vField = Peek('Field_new', 'Table2');

Drop Table Table2;

LIB CONNECT TO database2;

Data:

Load *;

sql select * from

db2.abc

where Field_table2 in $(vField)

[aarkay29]

Try

LIB CONNECT TO database2;

Data:

Load *;

sql select * from

db2.abc

where match(FieldName2,'$(vField)');

Note:FieldName2 is the name of the field in table  db2.abc which has a,b,c values

[achakilam1022]

Hi Aar,

I've received this error when I used match

check the manual that corresponds to your MySQL server version for the right syntax to use near ''')' at line 3

sql select * from db2.abc where match(FieldName2, '')

Thanks!

[CathyRDuvall]

where vField like ('a', 'b', 'c', 'd')

[aarkay29]

where match(FieldName2,$(vField));

use the above instead where match(FieldName2,'$(vField)');

and make sure the variable'vField' is not null

[achakilam1022]

Hi Cathy,

It's not a static list of values. Thanks!

[achakilam1022]

Aar,

Still, the same error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3

I know that "Field_new" is not null which I'm assigning to vField. How to make sure the variable is not null?

Thanks!

[aarkay29]

Use Trace

LIB CONNECT TO database1;

Table1:

Load Field,

1 as PlaceHolder;

select distinct Field from db1.xyz

where ....

;

Table2:

load

Concat(chr(39)&Field &chr(39),',') as Field_new

Resident Table1

Group by PlaceHolder;

Drop Table Table1;

let vField = Peek('Field_new', 'Table2');

Trace  vField is $(vField);  //or just use - Trace $(vField);

Drop Table Table2;

LIB CONNECT TO database2;

Data:

Load *;

sql select * from

db2.abc

where Field_table2 in ($(vField));    // This is correct syntax and should work if $(vField) is not null

[achakilam1022]

Aar,

vField is null. You think there is something wrong with my peek statement? Thanks!

[aarkay29 · Accepted solution]

try

let vField = Peek('Field_new',0, 'Table2');