10 Replies Latest reply: May 22, 2012 5:25 AM by Przemysław Wojda RSS

    How to refer to a variable?

    Przemysław Wojda

      Hi All,

       

      I'm looking for a way to refer to a DB_Name in the below WHERE condition (the one below is, obviously, not working). For the data model purpose I'd like to keep the condition within this bit, not load it into another table.

       

      DBs:
      SELECT DB_Name FROM Configuration;
      
      Data:
      LET NoOfDBs = NoOfRows('DBs');
      FOR i = 0 to $(NoOfDBs)-1
      LET DB_Name = Peek('DB_Name', $(i), 'DBs');
      SELECT
                '$(DB_Name)' AS DB_Name, 
           Dim_1
      FROM $(DB_Name).Table_1 WHERE $(DB_Name) NOT IN ('DB_1');
       
      NEXT
      

       

      Regards,

      Przemek

        • Re: How to refer to a variable?
          Miguel Angel Baeyens de Arce

          Hi Przemek,

           

          Is the field "DB_Name" spelled correctly or does it come from the database in table "DBs" all uppercase? Note that QlikView is case sensitive also for the names of the fields, so although the name may be uppercase in the SQL statement, it may be returned in lowercase (for example).

           

          Apart from that, set a TRACE after DB_Name just to check its value:

           

          LET DB_Name = Peek('DB_Name', $(i), 'DBs');
          TRACE $(DB_Name);
          


          Hope that helps.

           

          Miguel

          • Re: How to refer to a variable?
            Deepak Vadithala

            Hi Przemek,

             

            Adding to what Miguel suggested...

             

            Are you using SQL Server as your database engine? If yes, then you need to use following naming convention :

             

            DatabaseName.SchemaName.TableName

             

            or

             

            DatabaseName..TableName

            -- Here the two dots (..) represent default schema for the login

             

            So you need use something like this..

             

            _________________________________________________________________

            DBs:

            SELECT DB_Name FROM Configuration;

             

            Data:

            LET NoOfDBs = NoOfRows('DBs');

            FOR i = 0 to $(NoOfDBs)-1

            LET DB_Name = Peek('DB_Name', $(i), 'DBs');

            SELECT

                      '$(DB_Name)' AS DB_Name,

                 Dim_1

            FROM $(DB_Name).dbo.Table_1 WHERE $(DB_Name) NOT IN ('DB_1');

             

            NEXT

            _________________________________________________________________

             

            Hope this helps!

             

            Cheers,

            DV

            • Re: How to refer to a variable?
              jonathan dienst

              Hi

               

              The problem here is that the WHERE clause in the SELECT statement is not valid. This where clause is being passed to the database, and WHERE referencing a <tablename> is not syntax that the database server will understand. Where clauses filter on fields, not tables.

               

              If you want to selectively open table, you will need to wrap the SELECT statement with a Qlikview If statement.

               

              If '$(DB_Name)' <> 'DB_1' Then

               

                   SQL SELECT .....

               

              End If

               

              Hope that helps

              Jonathan