3 Replies Latest reply: Aug 30, 2011 1:44 PM by Miguel Angel Baeyens de Arce RSS

    Sql Server Join Problem

      Hi guys, I have been using QlikView and reading from flatfiles successfully, but I now have a requirement to read from a SQL Server database for my application.

       

      I managed to connect to the db without any issues, but I am just trying to take my SQL Server code and paste it in QlikView as follows:

       

      SQL SELECT

      top 200 * from dbname.dbo.view1

      inner join dbname.dbo.view2

      on dbname.dbo.view1.common_col = dbname.dbo.view2.common_col

       

      QlikView keeps throwing up an error on the join, but I know the join is correct, because it works perfectly in SQL Server.

       

      I also noticed as well that QlikView did not like the alias names I used for the tables which were working fine in SQL Server so I took them out. I am pretty sure I am missing something very obvious and would appreciate it if someone can tell me where I am going wrong so i can proceed.

       

      Thanks

        • Re: Sql Server Join Problem
          Miguel Angel Baeyens de Arce

          Hi,

           

          Note that when you use SQL the following SELECT is passed by the driver on to the database server, so it has nothing to do with QlikView, to the extent that the driver may not understand the statement even when it works in the query analyzer as it happens in your case, but the query is neither interpreted nor executed by QlikView.

           

          But whay it might be happening is that the returning set has more than one field named alike, and since this is not allowed in QlikView, hence the error. That's why I strongly recommed to always use the LOAD part, so you are in full control on what you pull from the database and put into memory, renaming when necessary. If this is what is happening, though, you will need to rename in the SELECT statement, so all fields come with different names when loaded into QlikView.

           

          Say for example that your working query in SQL but not in QlikView is (names are fake)

           

          SQL SELECT
          top 200 dbname.dbo.view1.common_col, dbname.dbo.view1.ID, dbname.dbo.view2.ID from dbname.dbo.view1
          inner join dbname.dbo.view2
          on dbname.dbo.view1.common_col = dbname.dbo.view2.common_col
          

           

          You have two ID fields when it comes to QlikView loading, so you will need to rename at least one of them

           

          SQL SELECT
          top 200 dbname.dbo.view1.common_col, dbname.dbo.view1.ID, dbname.dbo.view2.ID AS ID2 from dbname.dbo.view1
          inner join dbname.dbo.view2
          on dbname.dbo.view1.common_col = dbname.dbo.view2.common_col
          

           

          So the final script looks like

           

          TableName:
          LOAD common_col,
               ID,
               ID2;
          SQL SELECT
          top 200 dbname.dbo.view1.common_col, dbname.dbo.view1.ID, dbname.dbo.view2.ID AS ID2 from dbname.dbo.view1
          inner join dbname.dbo.view2
          on dbname.dbo.view1.common_col = dbname.dbo.view2.common_col
          

           

          Hope that helps.

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica

            • Sql Server Join Problem

              Thanks a lot Miguel.

               

              Your correct answer has led to another question.

               

              For some fields, when I put them in the Load part so I can control the select statement, they error saying field not found, but the fields actually exist.

               

              Just wondering if you have come across that also and know a way to handle this.

               

              Cheers

                • Re: Sql Server Join Problem
                  Miguel Angel Baeyens de Arce

                  Hi,

                   

                  That's not unsual either. Check that fields are spelled exactly as they are in the database, not in the SQL SELECT statement. QlikView is case sensitive for both field names and values. Assuming all fields in the database are uppercase, the following will work in both QlikView and your query analyzer:

                   

                  SQL SELECT id, name, address
                  FROM database.table;
                  

                   

                  However, the correct LOAD statement will look like

                   

                  Customer:
                  LOAD ID,
                       NAME,
                       ADDRESS;
                  SQL SELECT id, name, address
                  FROM database.table;
                  

                   

                  If the driver returns the fields uppercase even when you wrote them lowercase, then QlikView will read them uppercase. So if in the example you spell instead

                   

                  Customer:
                  LOAD ID,
                       name, // this will fail, this field doesn't exist because "name" <> "NAME"
                       ADDRESS;
                  SQL SELECT id, name, address
                  FROM database.table;
                  

                   

                  Hope that helps.

                   

                  Miguel Angel Baeyens

                  BI Consultant

                  Comex Grupo Ibérica