14 Replies Latest reply on Mar 19, 2014 7:23 AM by Justin Larson

    are common table expressions (CTE) supported in custom sql statements?

    Justin Larson

      That's it. That's the whole question. When I try to use a CTE, I get the error

       

      [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 ')'.

       

      Which implies that they are supported, and that I just got the syntax wrong (though the query executes just fine in SQL Server).

       

      So when I try to put a semicolon at the beginning, I get this error:

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

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

       

       

      Are they not supported, or is there some special syntax needed here?

        • 1. Re: are common table expressions (CTE) supported in custom sql statements?
          Dru Duos

          I use Oracle, and common table expressions are called subquery factoring by Oracle.  I use CTE/subquery factoring all the time and have never had an issue.

           

          In Oracle we normally end SQL statements with a semi-colon and I have found that Tableau does not like the semi-colon.  Most of the SQL issues I've had are related to the semi-colon or duplicated fields in the SELECT clause that produces the data output (the outer query in a CTE).

          • 2. Re: are common table expressions (CTE) supported in custom sql statements?
            Justin Larson

            Yeah, I haven't had a problem with subqueries. Some of the time it would be possible to refactor CTE's as subqueries, but it's bunch of copy/paste work to move code around, and it's nice to be able to just paste in existing code (plus subqueries are not always an appropriate/plausible substitute).

             

            Guess I should have specified a little more clearly outside of my tags - I'm using T-SQL for a connection to SQL Server 2012 here.

            • 3. Re: are common table expressions (CTE) supported in custom sql statements?
              Dru Duos

              T-SQL CTE's and Oracle Subquery Factoring are both ANSI-99 implementations of common table expressions.  Oracle just calls CTE's "subquery factoring"  the structure for subquery factoring in Oracle is

              with tablename1 as (inner query),

              tablename2 as (inner query),

              tablename3 as (inner query)

              Select

              from

              tablename1

              join....

               

              so I think we are talking about the same ANSI-99 protocol and query structure....

              1 of 1 people found this helpful
              • 4. Re: are common table expressions (CTE) supported in custom sql statements?
                Justin Larson

                Yep, that's exactly the same structure as SQL Server's.

                 

                So when I have a query that looks just like that, I get the error listed above. Sounds like the problem may be specific to SQL Server (?) Any ideas as to what that's trying to tell me

                 

                I'd accept guesses at this point. Tableau Support can't tell me if they are supported, which is why I'm here drawing at straws.

                • 5. Re: are common table expressions (CTE) supported in custom sql statements?
                  Dru Duos


                  1. Make sure you have current drivers, www.tableausoftware.com/support/drivers I had to install the Oracle drivers for 64bit client on my local computer when I installed the Tableau Desktop 8.1.3 64bit

                  2. Tableau does not seem to like SQL that ends with a semi-colon, so I don't end SQL statements with the semi-colon

                   

                  Tableau error messages about SQL syntax have rarely pointed to the issue...sometimes I've looked at the log file on my local computer to find Tableau was trying to cast a ZIP code as an Integer when it hit a value it couldn't cast to integer...but the error Tableau displayed was a SQL syntax error.

                   

                  Check out the log files on your local computer.  They may be in c:\ my documents/My Tableau Repository/ Logs, but it depends on the version of Tableau Desktop you are running...that may help you identify the source of the error..

                   

                  The most common issue I've had is when I have duplicated a field in the output...so double check the output field list...two fields with the identical name will cause a problem...but the error message from Tableau does not point to duplicated fields

                   

                  I've also had issues when aliasing field names on occasion...if you are aliasing your output fields, keep the names simple, don't use spaces in the alias...

                  • 6. Re: are common table expressions (CTE) supported in custom sql statements?
                    Justin Larson

                    Confirmed drivers are up to date. I don't have any semicolons at all in the statement, so that's not causing it. Strangely, the error suggests that it actually wants/expects a semicolon.

                     

                    No problems with dupe field names. I keep aliases pretty clean, but I'll experiment with removing them entirely, though I've never had a problem with them in other query formats, including when I just reformat as a subquery with all the same aliasing.

                     

                    I will check out the log next opportunity and report back.

                     

                    Thanks for the pointers.

                    • 7. Re: Re: are common table expressions (CTE) supported in custom sql statements?
                      Justin Larson

                      FYI, just testing, I ran this in custom SQL box:

                       

                      with table1 as (select 1 ColumnName)

                      , table2 as (select 2 ColumnName)

                      , unionthem as (select columnname from table1 union select columnname from table2)

                       

                      select * from unionthem

                       

                       

                      returns this error:

                      [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 ','.

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

                      • 8. Re: Re: are common table expressions (CTE) supported in custom sql statements?
                        Justin Larson

                        Good pointer with the log.

                         

                        So Tableau runs the query wrapped as a subquery to get at metadata like

                         

                        select top 1 * from ( [All your Custom SQL here] ) as [TableauSQL]

                         

                        So the metadata query looked like this:

                         

                        SELECT TOP 1 *

                        FROM (

                          with table1 as (select 1 ColumnName)

                          , table2 as (select 2 ColumnName)

                          , unionize as (select columnname from table1 union select columnname from table2)

                         

                          select * from unionize

                        ) [TableauSQL]

                         

                        The problem here is that you can't have a subquery start with a common table expression. In this context, the error message makes total sense, it's the same error message I would get if I tried to run the whole SELECT TOP 1 query directly in SQL Server. This also explains why it doesn't like it when you end your queries with semicolons.

                         

                        So, error explained - the question remains, however, how do you make a CTE work? Perhaps just as importantly, why would it work in Oracle and not in SQL Server? Do you have the time to run that same sample CTE in Oracle and post your log/experience back here?

                        • 9. Re: are common table expressions (CTE) supported in custom sql statements?
                          Dru Duos

                          I ran a modified version in Oracle

                          with test1 as (select '1' as col1 from dual),

                          test2 as (select '2' as col1 from dual),

                          unionthem as (select col1 from test1 union select col1 from test2)

                          Select * from unionthem

                           

                           

                          When I look at the log file I see the same sort of wrapping

                           

                          2014-03-18 15:24:15.098 (-,-,-,-) 09cc: <QUERY protocol='04c9d030'>

                          2014-03-18 15:24:15.098 (-,-,-,-) 09cc: SELECT *

                          2014-03-18 15:24:15.098 (-,-,-,-) 09cc: FROM (

                          2014-03-18 15:24:15.098 (-,-,-,-) 09cc:   with test1 as (select '1' as col1 from dual),

                          2014-03-18 15:24:15.098 (-,-,-,-) 09cc:   test2 as (select '2' as col1 from dual),

                          2014-03-18 15:24:15.098 (-,-,-,-) 09cc:   unionthem as (select col1 from test1 union select col1 from test2)

                          2014-03-18 15:24:15.098 (-,-,-,-) 09cc:   select * from unionthem

                          2014-03-18 15:24:15.098 (-,-,-,-) 09cc: ) "TableauSQL"

                          2014-03-18 15:24:15.098 (-,-,-,-) 09cc: WHERE (0 = 1)

                          2014-03-18 15:24:15.098 (-,-,-,-) 09cc: </QUERY>

                           

                           

                          In Oracle I can successfully execute

                           

                          select * from (with test1 as (select '1' as col1 from dual),

                          test2 as (select '2' as col1 from dual),

                          unionthem as (select col1 from test1 union select col1 from test2)

                          select * from unionthem)

                           

                          I did test a CTE with T-SQL and got the same results as you...I noticed there were OBDCProtocol messages...I'd submit this as a service request to Tableau.  It appears there is an issue with how Tableau is wrapping or parsing the MS SQL statement, which is creating issues

                           

                          CTE's work fine in Oracle and I'd go nuts if I could not use them....the dataset we need are too complex and require that we use CTE's

                          1 of 1 people found this helpful
                          • 10. Re: are common table expressions (CTE) supported in custom sql statements?
                            Dru Duos

                            Not sure that I was clear, I was successful in running the Oracle statements in Tableau, producing an Extract or as a live connection

                            • 11. Re: are common table expressions (CTE) supported in custom sql statements?
                              Justin Larson

                              Yes, after finding the Tableau backend subquery, I confirmed, SQL Server simply doesn't support CTEs in a subquery. They have to be the first statement.

                               

                              So even though they are an ANSI standard, it really is the database that isn't compatible, or more specifically, because of the way Tableau collects metadata, that makes them incompatible.

                               

                              If Tableau used a different mechanism to scan metadata, there is no reason this shouldn't work. I don't imagine there is anything I can do to change the way that works, aside from putting in a feature request.

                               

                              Thanks for all your help! Glad to just have an answer.

                              • 12. Re: are common table expressions (CTE) supported in custom sql statements?
                                Justin Larson

                                In case anyone else comes across this thread, I came up with a cheesy workaround that will work sometimes.

                                 

                                If you wrap your CTE query in an OPENQUERY() command, it's executable.

                                 

                                On our simple example from the thread, it would look like this:

                                 

                                select * from openquery

                                (

                                [ServerName],

                                '     /* paste in your CTE query text here */

                                with table1 as (select 1 columnname)

                                , table2 as (select 2 columnname)

                                , unionthem as (select columnname from table1 union select columnname from table2)

                                 

                                select * from unionthem

                                '

                                )

                                 

                                There are a number of drawbacks to this approach, of which I will highlight 4.

                                 

                                1) readability - what a hot mess that openquery is. A bunch of the time, simply using subqueries would be clearer anyway

                                2) the query text is bounded by the single quotations, and it has an 8000 character limit - this means all those super complex queries you were hoping to accomplish are probably too long to put through this method anyway

                                3) because the whole query is wrapped in single quotes, in order for it to work any single quotes in your actual query text must be replaced with '' (two single apostrophes, not one double quote)

                                4) the function was designed to be used to cross servers, so you have to have a linked server set up and execute the code from the linked server. Kinda' wonky.

                                 

                                Anyway, there's a workaround, messy and hole-ridden as it is.

                                1 of 1 people found this helpful
                                • 13. Re: are common table expressions (CTE) supported in custom sql statements?
                                  Dru Duos


                                  Another possible solution:

                                   

                                  If you have rights in the MS SQL Database, you can create a view that is based on your CTE.  Then your custom SQL statement you use is Tableau is a simple Select ....from viewname.

                                   

                                  MS SQL allows you to create a view that is based on a CTE query

                                  CREATE OR REPLACE VIEW

                                  TEST AS

                                  WITH TABLE 1 AS ......

                                  • 14. Re: are common table expressions (CTE) supported in custom sql statements?
                                    Justin Larson

                                    Well, yes. I suppose I didn't mention that because I figured it was a given. I regularly connect to views with CTEs, but I also have regular needs for custom SQL that don't really warrant creating objects to permanently persist in the database.