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:
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...
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]
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!
I think your cte syntax might be the problem. try
; with MyCTE (field1, field2, field3)
(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.).
Hi, I'm trying to do the exact same thing, and having no luck. Any other thoughts?
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
Yes please. This functionality would be very useful. I strongly agree with the above suggestion.
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.