Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Using a table column in SQL Where Syntex

Hi,

I have a list of IDs ("IDRegister" from list SITES) which need to filter in the where function of a SQL connection.

But i cant find the right way to filter these IDs in my SQL Expression:

SITES:

LOAD

ows_SiteName as Customer,

ows_IDRegister as IDRegister

From [SharePoint List];

WCINBOQ:

LOAD

  Id,

    DataHardware as WCInBoQ3HWDate,

    DataSoftware as WCInBoQ3SWDate,

    DataMI as WCInBoQ3MIDate,

    DataINFRA as WCInBoQ3INFRADate;

SQL SELECT *

FROM CSU.dbo."Data_Boq" WHERE Id IN '$(IDRegister)';

With this code i am receiving this error:

1.PNG

With this one, it does not shows an error, but cant find any value.

SQL SELECT *

FROM CSU.dbo."Data_Boq" WHERE Id = '$(IDRegister)';

S

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Using a table column in SQL Where Syntex

First of all the IN operator in SQL needs to have it's values in paranthesis/elipsis  ()

Like this

SQL SELECT *

    FROM CSU.dbo."Data_Boq" WHERE Id IN (.....)  ;

Then the variable IDRegister has to be a list of values ... a table in Qlik is not a list. You will have to use the

Concat() function to generate a list like this:

LOAD
  
Concat( DISTINCT IDRegister , ',') AS List
  
GROUP BY One;
LOAD 1 AS One, * RESIDENT SITES;
InList
= Peek('List');

SQL SELECT *
FROM CSU.dbo."Data_Boq" WHERE Id IN
($(InList));

4 Replies
bpick000
New Contributor II

Re: Using a table column in SQL Where Syntex

The error seems to indicate that this is a problem with the way your SQL command is constructed. The error show that the Where clause is passed to SQL as: Where Id IN ''

The problem is that your variable (IDRegister) returns a null value in this script so the SQL's Where clause is constructed with a null.

Your version of SQL may not allow nulls in the Where...In statements. Try typing the SELECT statement as it is in the error message into your SQL editor and see what happens.

MVP
MVP

Re: Using a table column in SQL Where Syntex

First of all the IN operator in SQL needs to have it's values in paranthesis/elipsis  ()

Like this

SQL SELECT *

    FROM CSU.dbo."Data_Boq" WHERE Id IN (.....)  ;

Then the variable IDRegister has to be a list of values ... a table in Qlik is not a list. You will have to use the

Concat() function to generate a list like this:

LOAD
  
Concat( DISTINCT IDRegister , ',') AS List
  
GROUP BY One;
LOAD 1 AS One, * RESIDENT SITES;
InList
= Peek('List');

SQL SELECT *
FROM CSU.dbo."Data_Boq" WHERE Id IN
($(InList));

MVP
MVP

Re: Using a table column in SQL Where Syntex

And beware that a large number of values - that is a big list might cause a problem - either with the ODBC-driver or the SQL database... I think you could easily have hundreds of values without a problem and possible a few thousands...

Also beware that if your ID's aren't numbers they have to be quoted too which will have to be done like this in the Concat() part:

   Concat( DISTINCT Chr(39) & IDRegister & Chr(39) , ',' ) AS List

The Chr(39) is the way to generate a single quote character ...

MVP
MVP

Re: Using a table column in SQL Where Syntex

- sql IN needs (),

select ...... where field in ()

add ()

- variable IDRegister is null or empty

add some trace of that variable and try untl you get something like (Petter suggestion)

'value1', 'value2', 'value3'    for string or without quotes for num

Community Browser