答案

1. 選擇資料 > 新建資料來源，並選擇所需的資料來源。
2. 在「伺服器連接」對話方塊中，按一下初始 SQL。
3. 在「初始 SQL」欄位中輸入您的 CTE。
   以下的範例使用「員工」表上的遞歸自加入用名為 OrganizationChart 的通用表運算式構建員工報告階層。語法根據資料來源略有不同。 
   
   對於 SQL Server，我們在查詢結尾使用「SELECT INTO [Temp Table Name]」而非「SELECT」，建立本機暫存表： 
   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
   
   對於 Teradata，我們使用 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
   
   若使用 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. 建立一個自訂 SQL 查詢以從您將記錄插入的暫存表中選擇。 
   例如，在 SQL Server 中： 
   SELECT * from #ctedemo
   在 Teradata 中： 
   SELECT * from ctedemo
   在 IBM DB2 中： 
   SELECT * FROM SESSION.ctedemo
    

其他資訊