Reporting

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.