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!
1 of 1 people found this helpful
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.
Thanks for the inputs,
do you think should i give ; before my CTE SQL ?
Attached the examplw
WITH ACSS_TRAN_CTE AS (
SEL MRCH_NBR,MCC_CDE,TOT_TRAN_AMT,TRAN_CDE ,ACQ_ID ,MRCH_CNTRY_CDE FROM T_EDW1.ACSS_TRAN
WHERE PROC_DT BETWEEN '2017-09-01' AND '2018-08-31' AND TRAN_CDE IN('1720','1700')
NNMD_CLKP_CTE AS (
SEL DISTINCT NNMD.ACQ_ID,NOVUS_MRCH_NBR FROM
SEL NOVUS_MRCH_NBR , ACQ_ID FROM T_MDSS.NNMD_CLKP
1 of 1 people found this helpful
I had this problem and what worked for me is created a view with the cte. In Tableau, i just used the view and worked without any issues.
I realize this may not be a solution for some organizations who dont allow viz developers creating db objects.