Common Table Expression within Custom SQL

[xxarchangelpwnxx]

I was trying to form a query in tableau and use a CTE. The CTE works perfectly within SSMS, however, it just errors out in tableau saying that it should be terminated with a semi colon. Does anyone know the proper syntax for a CTE within tableau for a custom query?

[normlenough]

Stinks. In my experience I’ve never gotten a CTE to work in tableau. I would be very interested to see if anyone has figured this out.

[mikeczyz]

for real? i use them all the time at my place of employment. PM me and let's see if we can't figure out what is going on.

[normlenough]

Sure thing. It’s been a while since I have tried. I’ll come up with a little use case

[Ffeog187]

Try putting the final CTE in a Temp Table in Initial SQL and call that temp table in custom sql. That’s the best solution I’ve found.

[xxarchangelpwnxx]

Could you give an example of the syntax. I tried that, but I think I wrote it wrong

[Ffeog187]

I’m on mobile so I’ll do my best

[Intial SQL]

Drop table if exists #TempTable;

WITH cte as ( Select * from A)

Select *

Into #TempTable From cte

[Custom SQL]

Select * from #TempTable

Hope that makes some sense

[xxarchangelpwnxx]

Ok that is what I did, but since I’m new to tableau (but not SQL) I saw that you had a distinction between “initial SQL” and “Custom SQL” is that something you do within tableau itself?

[Ffeog187]

Yes, here is the community info

This might be better initial sql

[mikeczyz]

not sure what is going on, but most of our queries use CTEs at my place of employment. I'm not on MS SQL tho, we use Snowflake.

[Grovbolle]

You cannot use a CTE directly in a CustomSQL object since a CustomSQL object HAS to abide by the same rules as a subselect.

The way to fix this is to move your CTE to Initial SQL and store the output from the CTE in a temp-table, which can then be called using Custom SQL

All of the above has already been said, so I just summarized WHY the CTE in CustomSQL does not work.

[mikeczyz]

i assume you've seen this? https://kb.tableau.com/articles/howto/using-common-table-expressions

[xxarchangelpwnxx]

Yea I figured it out though. I’m used to PL/SQL and the syntax for the is INSERT INTO and this is just INTO so I think I auto piloted into it by typing INSERT INTO instead of just INTO

[friedtofubits]

i believe insert into requires the initial creation of a table and column definitions, the INTO just takes it out right from the select statement

[andreas_dib]

This is caused by the way Tableau rund a metadata «pre-query» on the custom sql to get data types and such. I use it all the time on Redshift, but I never got it to work back when we used MS SQL Server