7 Replies Latest reply on Aug 14, 2018 1:29 PM by Hari Ankem

    With.. As Clause not allowed in Custom SQL (Need help with initial SQL syntax)

    gxu

      Hi, I'm writing a complicated sql query and used a lot of 'with..as' to name a block of sub-query. It's working in SQL server, but not in Tableau Custom SQL.

      I researched on the error and looks like I need to use initial SQL.  I already looked at the online help(Run Initial SQL), but still don't understand how to use it, can someone help me with the syntax of initial SQL? I tried to copy the with..as clause into initial sql, which didn't work.

       

      My query looks like this:

       

      With Sales as (Select Column12345 from XXX),

      COGS as (Select Column12345 from XXX),

      Expense as (Select Column12345 from XXX)

       

      Select column 1, column 2, column 3 from Sales

      Union

      Select column 1, column 2, column 3 from COGS

      Union

      Select column 1, column 2, column 3 from Expense

        • 1. Re: With.. As Clause not allowed in Custom SQL (Need help with initial SQL syntax)
          Hari Ankem

          Try putting a semi-colon after the first With clause.

           

          With Sales as (Select Column12345 from XXX),

          COGS as (Select Column12345 from XXX),

          Expense as (Select Column12345 from XXX);

           

          Select column 1, column 2, column 3 from Sales

          Union

          Select column 1, column 2, column 3 from COGS

          Union

          Select column 1, column 2, column 3 from Expense

           

          If the above does not work, you can try splitting the above single With statement into multiple With statements, one for Sales, COGS and Expense.

          • 2. Re: With.. As Clause not allowed in Custom SQL (Need help with initial SQL syntax)
            Ankit Bansal

            Grace,

             

            Your query should work with custom sql ideally. You can try this:

             

            Select column 1, column 2, column 3 from (Select Column12345 from XXX)

            Union

            Select column 1, column 2, column 3 from (Select Column12345 from XXX)

            Union

            Select column 1, column 2, column 3 from (Select Column12345 from XXX)

            • 3. Re: With.. As Clause not allowed in Custom SQL (Need help with initial SQL syntax)
              gxu

              I already tried to put semicolon, as this is what the error message tells me to do as well, but it's not working. I got the same error message even after put semicolon.

               

              An error occurred while communicating with Microsoft SQL Server.

               

              Bad Connection: Tableau could not connect to the data source.

              [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'With'.

              [Microsoft][ODBC Driver 13 for SQL Server][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][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ','.

              [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ')'.

              • 4. Re: With.. As Clause not allowed in Custom SQL (Need help with initial SQL syntax)
                gxu

                The reason I need to use with clause is because each sub-query is long and complex, so I prefer to keep it that way.

                • 5. Re: With.. As Clause not allowed in Custom SQL (Need help with initial SQL syntax)
                  Hari Ankem

                  OK. Let's try another way then.

                   

                  Put the following in the Initial SQL:

                   

                  DROP TABLE IF EXISTS Sales;

                  DROP TABLE IF EXISTS COGS;

                  DROP TABLE IF EXISTS Expense;

                   

                  CREATE TABLE Sales AS

                  Select column1, column 2, column 3, column 4, column 5 from XXX;

                   

                  CREATE TABLE COGS AS

                  Select column1, column 2, column 3, column 4, column 5 from XXX;

                   

                  CREATE TABLE Expense AS

                  Select column1, column 2, column 3, column 4, column 5 from XXX;

                   

                  Put the following in the Custom SQL:

                  Select column 1, column 2, column 3 from Sales

                  Union

                  Select column 1, column 2, column 3 from COGS

                  Union

                  Select column 1, column 2, column 3 from Expense

                  • 6. Re: With.. As Clause not allowed in Custom SQL (Need help with initial SQL syntax)
                    gxu

                    Still not working Hari.. Below is the error message. Also, I only have Read access to SQL Server, I'm not sure if I can use 'Create'.

                     

                    An error occurred while communicating with Microsoft SQL Server.

                     

                    Bad Connection: Tableau could not connect to the data source.

                    [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'IF'.

                    [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'SELECT'.

                    Initial SQL Error. Check that the syntax is correct and that you have access privileges to the requested database.

                    • 7. Re: With.. As Clause not allowed in Custom SQL (Need help with initial SQL syntax)
                      Hari Ankem

                      I am wondering if it has anything to do with the ODBC driver you are using, and/or the SQL-Server and Tableau versions. I don't have access to SQL*Server database here and so do not know what the reason could be. So, the only suggestion I can now give is to write as this:

                       

                      Put the following in the Custom SQL:

                      Select column1, column 2, column 3, column 4, column 5 from (Select column1, column 2, column 3, column 4, column 5 from XXX) AS Sales

                      Union

                      Select column1, column 2, column 3, column 4, column 5 from (Select column1, column 2, column 3, column 4, column 5 from XXX) AS COGS

                      Union

                      Select column1, column 2, column 3, column 4, column 5 from (Select column1, column 2, column 3, column 4, column 5 from XXX) AS Expense