While trying to use CTE (common table expression) in Table you might have got and error “tableau an error occurred while communicating with the data source SQL”.
Let us investigate this problem closely by creating two tables, write a CTE that use that use both tables and use the same table expression in Tableau.
--create tables if object_id('table1','U') is not null drop table table1; create table table1(col1 int); if object_id('table2','U') is not null drop table table2; create table table2(col2 int); if object_id('sequence1','SO') is not null drop sequence sequence1; --create sequences create sequence sequence1 as bigint start with 1 increment by 1 no cycle no cache go if object_id('sequence2','SO') is not null drop sequence sequence2; create sequence sequence2 as bigint start with 1 increment by 1 no cycle no cache go --inset tables insert into table1 select next value for sequence1; go 10 insert into table2 select next value for sequence2; go 10 --select tables with tb1 as ( select * from table1 ) select tb1.col1,tb2.col2 from tb1 join table2 tb2 on tb1.col1=col2; /* drop table table1; drop table table2; drop sequence sequence1; drop sequence sequence2; */
As you may see below the above select statement gives a result set same as below.
But if we use the same table expression in Tableau we will get some error message like below.
If you run SQL Profiler while executing this statement in Tableau you will see that instead of sending that actual SQL to the server tableau wraps it with SELECT TOP 1 * ()[Custom SQL Query] which leads to failer to execute.
To get around this problem you may replace your CTE with sub select statements like.
--sub-select select tb1.col1,tb2.col2 from table2 tb2 join (select * from table1)tb1 on tb1.col1=col2
Disclaimer: In the examples used above we could just get away by using a simple join statements. I have used CTE and sub select for demonstration purpose.