KNOWLEDGE BASE

Using Common Table Expressions


Published: 20 Dec 2016
Last Modified Date: 01 Mar 2017

Question

How to use Common Table Expressions (CTEs) in Tableau.
 

Environment

  • Tableau Desktop
  • SQL Server
  • Teradata
  • IBM DB2

Answer

  1. Select Data > New Data Source and choose your desired data source.
  2. In the Server Connection dialog box, choose Initial SQL.
  3. Enter your CTE in the Initial SQL field.
    The example below uses a recursive self-join on 'Employees' table to build out an employee reporting hierarchy using a common table expression named OrganizationChart. Syntax differs slightly depending on the data source. 

    For SQL Server, we create a local temporary table by using SELECT INTO [Temp Table Name] rather than SELECT at the end of the query: 
    WITH OrganizationChart (Id, [Name],  [Level], ManagerID) AS (
    SELECT
    Id, [Name], 0 as [Level], ManagerID
    FROM
    [dbo].[Employees] emp
    WHERE
    ManagerID IS NULL
    UNION ALL
    SELECT
    emp.ID, emp.[Name], [Level] + 1, emp.ManagerID
    FROM [dbo].[Employees] emp
    INNER JOIN OrganizationChart d ON
    emp.ManagerID = d.Id
    )
    SELECT  [Id], [Name], [Level], [ManagerID] INTO #ctedemo from OrganizationChart


    For Teradata, we create a temporary table by using CREATE VOLATILE TABLE [Temp Table Name]: 
    CREATE VOLATILE TABLE ctedemo AS (WITH RECURSIVE OrganizationChart (Id, Name,  Level, ManagerID) AS (
    SELECT
    Id, Name, 0 as Level, ManagerID
    FROM
    TEST.Employees emp
    WHERE
    ManagerID IS NULL
    UNION ALL
    SELECT
    emp.ID, emp.Name, Level + 1, emp.ManagerID
    FROM TEST.Employees emp
    INNER JOIN OrganizationChart d ON
    emp.ManagerID = d.Id
    ) SELECT  Id, Name, Level, ManagerID from OrganizationChart) WITH DATA NO PRIMARY INDEX
    ON COMMIT PRESERVE ROWS


    For IBM DB2 (Tableau Desktop 10.2 and newer versions):
    DECLARE GLOBAL TEMPORARY TABLE SESSION.ctedemo(ID int, Name varchar(50), Level int, ManagerID int) on commit preserve rows with replace not logged;
    INSERT INTO SESSION.ctedemo(ID, Name, Level, ManagerID) WITH OrganizationChart (Id, Name,  Level, ManagerID) AS (
    SELECT
    Id, Name, 0 as Level, ManagerID
    FROM
    Employees emp
    WHERE
    ManagerID IS NULL
    UNION ALL
    SELECT
    emp.ID, emp.Name, Level + 1, emp.ManagerID
    FROM Employees emp, OrganizationChart d
    where
    emp.ManagerID = d.Id
    )
    SELECT  Id, Name, Level, ManagerID from OrganizationChart

     
  4. Create a Custom SQL query that selects from the temp table you inserted the records into. 
    For example, in SQL Server: 
    SELECT * from #ctedemo
    In Teradata: 
    SELECT * from ctedemo
    in IBM DB2: 
    SELECT * FROM SESSION.ctedemo
     
With an employees table like the following:

ID

Name

ManagerID

1

John

NULL

2

Jim

1

3

Jane

1

4

Tom

3

5

Bob

4

6

Rob

3

7

Mike

1

The above solution would return a result like this: 

ID

Name

Level

ManagerID

1

John

0

NULL

2

Jim

1

1

3

Jane

1

1

7

Mike

1

1

4

Tom

2

3

6

Rob

2

3

5

Bob

3

4

Additional Information

When Tableau queries a data source, Custom SQL is passed in a query like the following:
SELECT TOP 1 * from ( [All your Custom SQL here] ) as [TableauSQL]
SQL Server and Teradata do not support CTE use in a subquery, so the result is an error.
 
Did this article resolve the issue?