Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
achakilam1022
Contributor II

Multiple values from variable in where condition in SQL query

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)

1 Solution

Accepted Solutions
aarkay29
Valued Contributor

Re: Multiple values from variable in where condition in SQL query

try

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

10 Replies
aarkay29
Valued Contributor

Re: Multiple values from variable in where condition in SQL query

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
Contributor II

Re: Multiple values from variable in where condition in SQL query

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
New Contributor III

Re: Multiple values from variable in where condition in SQL query

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

aarkay29
Valued Contributor

Re: Multiple values from variable in where condition in SQL query

where match(FieldName2,$(vField));


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


and make sure the variable'vField' is not null

achakilam1022
Contributor II

Re: Multiple values from variable in where condition in SQL query

Hi Cathy,

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

achakilam1022
Contributor II

Re: Multiple values from variable in where condition in SQL query

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
Valued Contributor

Re: Multiple values from variable in where condition in SQL query

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
Contributor II

Re: Multiple values from variable in where condition in SQL query

Aar,

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

aarkay29
Valued Contributor

Re: Multiple values from variable in where condition in SQL query

try

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