4 Replies Latest reply on Oct 22, 2012 2:05 AM by thierryjakircevic

    Problem with a very simple JOIN statement

    thierryjakircevic

      Hi,

       

      After updating to the last version of Tableau Desktop I am having issues with a very simple Custom SQL connection such as:

       

      SELECT * FROM "Table1"

      INNER JOIN [Table2] on [Table1].CompanyId = [Table2].CompanyID

       

      The following error message pops up when I try to validate it:

       

       

      Running the exact same query in SQL Server Management Studio works perfectly.

       

      If I change the name of the 2 fields "CompanyID" to "CompanyID1" and "CompanyID2" then the code works in Tableau. But this is obviosuly not a desired solution as I would have to rename all my views...

       

      Can anyone share some light on this please?

       

      Thank you

       

      Thierry

        • 1. Re: Problem with a very simple JOIN statement
          sirajmandayippurath

          Hi Thierry,

           

          The issue here is that because Table1 and Table2 contains CompanyID as a column in them and since you have used 'SELECT * ', Tableau is being presented two columns called CompanyID. This would not be acceptable and hence the error. I suggest you handpick the columns that you would like to use on Tableau as opposed to using ' * '.

           

          Would that help?

           

          Cheers

          Siraj

          • 2. Re: Problem with a very simple JOIN statement
            thierryjakircevic

            Hi Siraj,

             

            Thanks for helping out. The problem is that I am using this view on a lot of different dashboards.

            By simply using a SELECT *, any changes I made to the View will be automatically applied to all the data sources using that connection.

             

            However, if I am writing this statement in full, I would need to update the custom SQL code in every single one of them every time I add a field.

             

            The code works perfectly in SQL Server, shouldn't it be the same in Tableau as well?

            I'm not 100% sure, but I tend to think it was working in the past.

             

            Thank you

            Thierry

            • 3. Re: Problem with a very simple JOIN statement
              sirajmandayippurath

              Hi Thierry,

               

              I think it is one for the Product Development team then....

               

              If you run the SQL in SQL Management Studio, you will see that it returns CompanyID twice - but then you could repeat the occurrence of a column any number of times on SQL. As Tableau uses the column names as the field names in the tableau data source, the only way I could think of getting this working was to either restrict the number of occurrences or have an alias for each repeat occurrence.

               

              However, if you do not have a need to write Custom SQL, except for having to join multiple tables, I would suggest using the 'Multiple Tables' option on the connection screen. In this approach, Tableau provides an alias for duplicate fields. Would that help?

               

              Cheers

              Siraj

              • 4. Re: Problem with a very simple JOIN statement
                thierryjakircevic

                Thank you Siraj.

                 

                I actually end up using the method you recommend (using "Multiple Tables").

                It solves the majority of my needs (except for a couple of connections where I need to add a WHERE clause to my Custom SQL).

                 

                Would be nice if Tableau could automatically rename the duplicate columns though.

                 

                Thank you again for helping out!