我有一个有列的表-- OBJECT_ID,NAME,PARENT_CONTAINER_ID。这个表包含了所有的文件夹名称和它们的ParentID。如果父ID为空,则它是根文件夹。我需要一份包含所有文件夹及其完整路径的报告。但是下面的查询抛出错误..
WITH containercte(OBJECT_ID, NAME, PARENT_CONTAINER_ID, LEVEL, treepath) AS
(SELECT OBJECT_ID, NAME, PARENT_CONTAINER_ID, 0 AS LEVEL, CAST(NAME AS VARCHAR(1024)) AS treepath FROM CONTAINER
WHERE PARENT_CONTAINER_ID IS NULL
UNION ALL
SELECT d.OBJECT_ID AS OBJECT_ID, d.NAME, d.PARENT_CONTAINER_ID,
containercte.LEVEL + 1 AS LEVEL,
CAST(containercte.treepath + '-> ' + CAST(d.NAME AS VARCHAR(1024)) AS VARCHAR(1024)) AS treepath FROM CONTAINER d
INNER JOIN containercte
ON containercte.OBJECT_ID = d.PARENT_CONTAINER_ID)
SELECT * FROM containercte ORDER BY treepath;
有人能告诉我这个查询出了什么问题吗?它抛出的错误是
“递归公用表表达式"CONTAINERCTE”的fullselect必须是两个或多个fullselect的联合,并且不能包括列函数、GROUP BY子句、HAVING子句、ORDER BY子句或包含ON子句的显式连接“
发布于 2018-07-12 01:53:24
在Db2 LUW中,不允许在递归公用表表达式中使用显式join语法。它们在其他地方都可以,但递归CTE是个例外。
重写查询以使用旧的连接语法重写
FROM CONTAINER d
INNER JOIN containercte
ON containercte.OBJECT_ID = d.PARENT_CONTAINER_ID)
至
FROM CONTAINER d,
containercte
WHERE containercte.OBJECT_ID = d.PARENT_CONTAINER_ID
AND ...
我建议在WHERE子句中添加一个停止条件,如下所示
AND LEVEL < 8
https://stackoverflow.com/questions/51285075
复制相似问题