12 Replies Latest reply on Apr 19, 2018 12:08 PM by Hari Ankem

    Custom SQL Query with inner join  works in SQL Server, but not Tableau

    David Marquardt

      Hello,

       

      I am using custom sql query in order to create an inner join.  While the query I made works in SQL Server Management Studio, I get the following in Tableau:

       

      An error ocurred while communicating with data source 'ShowData'.

       

      I have two questions:

       

      1. What does this error mean? I've seen it mentioned in two messages so far, but not sure what is the common thread (New Custom SQL - An error occured while communicating with datasource 'showdata' , Getting an error while trying to create a simple dashboard using New Custom SQL ). 

       

      2. Here is the script I'm using to rearrange data. Basic purpose is to take National data (classified as 10) that had been put in a regional variable (RegionId), and create a new variable to house the US values. So the sql query below takes the rows that are at the national level and joins it back up with the rest of the table as a value field (US Value).  In order to get a meaningful join, it uses a specific Level of Detail (commodity, category, etc.. must match). Any ideas what could be causing the issue?

       

      WITH USLevel AS (

      SELECT *

        FROM [Visualizations].[CostReturn].[View_PublishData2]

        WHERE [RegionId] = 10

      )

      , RegionLevel AS

      (

      SELECT * FROM [Visualizations].[CostReturn].[View_PublishData2] WHERE [RegionId] <> 10

      )

      SELECT r.*, us.Value AS 'US Value' FROM USLevel AS us INNER JOIN RegionLevel AS r ON

      us.CommodityId = r.CommodityId AND

      us.CategoryId = r.CategoryId AND

      us.CountryId = r.CountryId AND

      us.ItemId = r.ItemId AND

      us.UnitsId = r.UnitsId AND

      us.[Year] = r.[Year]

       

       

      Thank you,

       

      David

        • 1. Re: Custom SQL Query with inner join  works in SQL Server, but not Tableau
          Hari Ankem

          Can you try doing the following and then try executing the query again?

           

          1. Replace * with the actual column names.

          2. Remove the square brackets "[" and "]".

          • 2. Re: Custom SQL Query with inner join  works in SQL Server, but not Tableau
            David Marquardt

            Thank you for the reply, Hari.

             

            Do you recommend something like this?

             

             

             

             

            WITH USLevel AS (

             

             

            SELECT CommodityId, r.CategoryId, CountryId , ItemId, UnitsId, Year

             

             

              FROM Visualizations.CostReturn.View_PublishData2

             

             

              WHERE RegionId = 10

             

             

            )

             

             

            , RegionLevel AS

             

             

            (

             

             

            SELECT CommodityId, r.CategoryId, CountryId , ItemId, UnitsId, Year FROM Visualizations.CostReturn.View_PublishData2 WHERE [RegionId] <> 10

             

             

            )

             

             

            SELECT r.CommodityId, r.CategoryId, r.CountryId , r.ItemId, r.UnitsId, us.Year, us.Value AS 'US Value' FROM USLevel AS us INNER JOIN RegionLevel AS r ON

             

             

            us.CommodityId = r.CommodityId AND

             

             

            us.CategoryId = r.CategoryId AND

             

             

            us.CountryId = r.CountryId AND

             

             

            us.ItemId = r.ItemId AND

             

             

            us.UnitsId = r.UnitsId AND

             

             

            us.Year = r.Year

            • 5. Re: Custom SQL Query with inner join  works in SQL Server, but not Tableau
              Hari Ankem

              If you can let me know what's the error, I can probably help. It is possible that the database may be assuming some of the columns to be as keywords and so it may be failing.

              • 6. Re: Custom SQL Query with inner join  works in SQL Server, but not Tableau
                David Marquardt

                Here is the error I get: 

                 

                 

                An error ocurred while communicating with data source 'ShowData'

                 

                Is this a pretty generic error, or a sign of a specific problem?

                 

                 

                 

                • 7. Re: Custom SQL Query with inner join  works in SQL Server, but not Tableau
                  Hari Ankem

                  This seems more generic. Does it show any error details?

                  • 8. Re: Custom SQL Query with inner join  works in SQL Server, but not Tableau
                    David Marquardt

                    Thanks! I hadn't noticed the details option.

                     

                    I can run the first script in SQL Management Studio just fine (I can't get script to run without the brackets in my Management Studio - the scope of the pro):

                     

                    WITH USLevel AS (

                    SELECT *

                      FROM [Visualizations].[CostReturn].[View_PublishData2]

                      WHERE [RegionId] = 10

                    )

                    , RegionLevel AS

                    (

                    SELECT * FROM [Visualizations].[CostReturn].[View_PublishData2] WHERE [RegionId] <> 1

                    )

                     

                    SELECT r.*, us.Value AS 'US Value' FROM USLevel AS us INNER JOIN RegionLevel AS r ON

                    us.CommodityId = r.Commo

                    us.CategoryId = r.CategoryId AND

                    us.CountryId = r.CountryId AND

                    us.ItemId = r.ItemId AND

                    us.UnitsId = r.UnitsId AND

                    us.[Year] = r.[Year]

                     

                     

                     

                    Here's the detailed error I get:

                     

                    [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'WITH'.

                    [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

                    [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ','.

                    [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ')'.

                     

                     

                    Does this mean anything to you, besides - it's having issues from the get go?

                     

                    David

                    • 9. Re: Custom SQL Query with inner join  works in SQL Server, but not Tableau
                      maneesh.gaddam

                      David,

                       

                      Place the sql code containing 'WITH' statement in the Initial Sql window and then place the rest of the code in the usual 'New Custom Sql' window and it should work.

                      • 10. Re: Custom SQL Query with inner join  works in SQL Server, but not Tableau
                        Hari Ankem

                        Can you try this? I am simply rewriting the query in a different way. Also, the value column was missing in the statement you gave earlier, and there was a prefix "r." for the CategoryId which probably are a few causes for the query to fail. The Year and Value are keywords in SQL SERVER, and so I had to include the square brackets around them.

                         

                        SELECT r.CommodityId,

                               r.CategoryId,

                               r.CountryId,

                               r.ItemId,

                               r.UnitsId,

                               us.Calendar_Year,

                               us.cost_value AS 'US Value'

                        FROM (SELECT CommodityId,

                                     CategoryId,

                                     CountryId,

                                     ItemId,

                                     UnitsId,

                                     [YEAR] AS Calendar_Year,

                                     [VALUE] AS cost_value

                              FROM Visualizations.CostReturn.View_PublishData2

                              WHERE RegionId = 10) AS us

                          INNER JOIN (SELECT CommodityId,

                                             CategoryId,

                                             CountryId,

                                             ItemId,

                                             UnitsId,

                                             [YEAR] AS Calendar_Year,

                                             [VALUE] AS cost_value

                                      FROM Visualizations.CostReturn.View_PublishData2

                                      WHERE RegionId <> 10) AS r

                                  ON us.CommodityId = r.CommodityId

                                 AND us.CategoryId = r.CategoryId

                                 AND us.CountryId = r.CountryId

                                 AND us.ItemId = r.ItemId

                                 AND us.UnitsId = r.UnitsId

                                 AND us.Calendar_Year = r.Calendar_Year

                        1 of 1 people found this helpful
                        • 11. Re: Custom SQL Query with inner join  works in SQL Server, but not Tableau
                          David Marquardt

                          Yes, this worked!  Thank you.  I just needed to add a column in for the value of the region (all except for region id <> 10) at the beginning (in bold). It ended up looking like this:

                           

                          SELECT r.CommodityId,

                                 r.CategoryId,

                                 r.CountryId,

                                 r.ItemId,

                                 r.UnitsId,

                                 r.RegionId,

                                 r.cost_value,

                                 us.Calendar_Year,

                                 us.cost_value AS 'US Value'

                          FROM (SELECT CommodityId,

                                       CategoryId,

                                       CountryId,

                                       ItemId,

                                       UnitsId,

                                       RegionId,

                                       [YEAR] AS Calendar_Year,

                                       [VALUE] AS cost_value

                                FROM Visualizations.CostReturn.View_PublishData2

                                WHERE RegionId = 10) AS us

                            INNER JOIN (SELECT CommodityId,

                                               CategoryId,

                                               CountryId,

                                               ItemId,

                                               RegionId,

                                               UnitsId,

                                               [YEAR] AS Calendar_Year,

                                               [VALUE] AS cost_value

                                        FROM Visualizations.CostReturn.View_PublishData2

                                        WHERE RegionId <> 10) AS r

                                    ON us.CommodityId = r.CommodityId

                                   AND us.CategoryId = r.CategoryId

                                   AND us.CountryId = r.CountryId

                                   AND us.ItemId = r.ItemId

                                   AND us.UnitsId = r.UnitsId

                                 

                                   AND us.Calendar_Year = r.Calendar_Year