1 of 1 people found this helpful
(The ping didn't work but I found it anyway, Sean.)
The reason you can't use Common Table Expressions (i.e. the WITH clause) in Tableau's custom SQL connections is that a Common Table Expression has to be defined at the outermost level of the query. You say something like:
(rest of the SQL statement, which references the CTE)
That doesn't work in a custom SQL connection because the custom SQL expression actually becomes a sub-query in a larger statement constructed by Tableau.
For the same reason you can't use SQL Server "Query Hints" in a custom SQL connection (since they have to be defined at the outermost level), but you can use "Table Hints" (which are embedded within the query).
Thank you Richard, this is helpful to understand why WITH or hints do not work in custom SQL in Tableau.
Any known plans to change how Tableau handles custom SQL to allow WITH ?
(e.g. the SQL could be reformatted to move WITH and hints to an outer level)
I don't know, but I very much doubt that that will ever happen. Tableau has to support so many different SQL dialects, I doubt they will do something like tlhat which ie fairly specific to a few databases.
I think the nearest you are likely to get to the effect I of CTEs is defining yourself views in the database.
Thanks again for your response. This clarifies.
For the attention of Tableau development team:
WITH is part of the SQL:1999 standard, not a dialect by any means, and not something new. Oracle, SQL, MySQL, DB2 all support WITH.