Using a table column in SQL Where Syntex

[#1]

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:

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

[Anonymous]

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.

[petter · Accepted solution]

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));

[petter]

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 ...

[maxgro]

- 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