8 Replies Latest reply on Feb 28, 2012 3:57 AM by Brendon Smith

    Common Table Expressions (CTE's) - Sql Server

    . ktr73

      Hi - I'm trying to get a query working that has the form:

       

      With mycte As (

        Select * From some_table Where column = 1

      )

       

      Select * From mycte join some_other_table On mycte.x = some_other_table.x

       

      And I get an error something along the lines of:

       

       

      SQL Server database error 0x80040E14: Incorrect syntax near ')'.

      SQL Server database error 0x80040E14: 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.

      SQL Server database error 0x80040E14: Incorrect syntax near the keyword 'With'.

      Unable to connect to the server "EP-FINDB01\FINANCE". Check that the server is running and that you have access privileges to the requested database.

       

      Any idea why this won't work?  My query has no errors when I run it in SQL Server Studio Manager.  Thanks.

        • 1. Re: Common Table Expressions (CTE's) - Sql Server
          Richard Leeke

          I recall that I had the same issue when I tried using CTEs in a Custom SQL Connection.  I think I looked at the SQL Tableau generates and as I recall that just doesn't support CTEs.  Basically Tableau tried to wrap the Custome SQL up in an outer SQL statement like this:

           

          SELECT [fields]

          FROM ([custom SQL Connection SQL Statement])

          WHERE [filter criteia]

          ...

           

          and SQL server simply doesn't allow a CTE to be wrapped in an outer SQL statement like that.

           

          Or at least, that's what I think I remember I worked out last year sometime...

          • 2. Re: Common Table Expressions (CTE's) - Sql Server
            Joe Mako

            You could rewrite your SQL to something like:

             

             

            SELECT *
            
             FROM (SELECT *
            FROM [some_table]
            WHERE [some_table.[column] = 1
            ) AS [mycte]
            JOIN [some_other_table]
            ON [mycte].[x] = [some_other_table].[x]
            


            • 3. Re: Common Table Expressions (CTE's) - Sql Server
              . ktr73

              Thx - that's kind of what I was afraid of.  Regarding the rewrite, the problem is that I use the CTE multiple times (so I'd be repeating the query which is kind of a pain if I change it).  I ended up putting into a view and then "Select * From view" instead.  Thanks for your help!

              • 4. Re: Common Table Expressions (CTE's) - Sql Server
                Tim Costello

                I think your cte syntax might be the problem. try

                 

                ; with MyCTE (field1, field2, field3)

                as

                (select field1, field2, field3 from SomeTable where Something = 'good')

                select * from MyCTE

                 

                Note the semi colon.  You MUST terminate the line before a CTE definition with a semicolon.  Most people don't use semicolons that much so this catches a lot of people.  I usually just put the semicolon at the beginning of my cte definition so I don't have to worry about it.  If you did terminate the line prev to a cte definition with a semi colon then you should not start your cte with a semi colon.

                 

                also, you need to define the field names inside parenthesis like in my sample.  Yours didn't have that (but it might have been there in the real code.).

                 

                good luck.

                • 5. Re: Common Table Expressions (CTE's) - Sql Server
                  guest contributor

                  Hi, I'm trying to do the exact same thing, and having no luck.  Any other thoughts?

                   

                  Thank you.

                  • 6. Re: Common Table Expressions (CTE's) - Sql Server
                    David. Lewis

                    I'm having this issue as well.  The only solution other than the 'kludgy' (no offense intended) workarounds suggested above is to create a view on the sql server that uses the cte-s as required, then having tableau query that datasource.

                     

                    Tableau folks--it 'would be nice' if we could use raw sql with cte-s, without having to create views as a work around.  Often I am working out the data set as I build the tableau view, and it is convenient to go to the data tab and tweak things there without having to work directly on the database objects.  dl

                    • 7. Re: Common Table Expressions (CTE's) - Sql Server
                      D W

                      Yes please. This functionality would be very useful. I strongly agree with the above suggestion.

                      • 8. Re: Common Table Expressions (CTE's) - Sql Server
                        Brendon Smith

                        This is also a problem for me, using Tableau 6.0

                        Tableau people: any update of a fix for this?

                         

                        Ideally Custom SQL should just pass through the SQL statement to the DB for these and not add any additional restrictions to the syntax..

                         

                        Also would be great if we could run SQL blocks, and procs which return a result set within the Custom SQL option.