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:
   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
    

Additional Information