4 Replies Latest reply: Apr 27, 2015 4:07 PM by Massimo Grossi RSS

    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

        • Re: Using a table column in SQL Where Syntex
          Brian Pick

          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.

          • Re: Using a table column in SQL Where Syntex
            Petter Skjolden

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

              • Re: Using a table column in SQL Where Syntex
                Petter Skjolden

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

              • Re: Using a table column in SQL Where Syntex
                Massimo Grossi

                - 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