H2中的递归CTE:未返回任何数据 [英] Recursive CTE in H2: No data returned
问题描述
我正在尝试将专有的Oracle CONNECT BY
查询转换为将在H2上运行的标准SQL查询,并以相同的顺序生成相同的数据 。
I am trying to convert a proprietary Oracle CONNECT BY
query into a standard SQL query that will run on H2, and generate the same data in the same order.
这是Oracle查询,它的工作原理是:
This is the Oracle query, which works:
SELECT id, name, parent
FROM myschema.mytable
START WITH id = 1
CONNECT BY PRIOR id = parent
这就是我想的-但是,它在 ResultSet
中不返回任何行。
This is what I've come up - however, it returns no rows in the ResultSet
.
WITH RECURSIVE T(id, name, parent, path) AS (
SELECT id, name, '' AS parent, id AS path
FROM myschema.mytable WHERE id = 1
UNION ALL
SELECT ou.id, ou.name, ou.parent,
(T.path + '.' + CAST (ou.id AS VARCHAR)) AS path
FROM T INNER JOIN myschema.mytable AS ou ON T.id = ou.parent
) SELECT id, name, parent FROM T ORDER BY path
第一行和相关
我没有使用H2的Oracle兼容模式(顺便说一句,它不支持CONNECT BY)。
I am not using H2's Oracle compatibility mode (which doesn't support CONNECT BY, by the way).
推荐答案
这似乎是Anorm数据库访问库或JDBC驱动程序未正确替换查询参数的问题(查询替换为未显示在问题中,因为我认为它不相关。
This seems to have been a problem with either the Anorm database access library or the JDBC driver not substituting a query parameter correctly (the query substitution was not shown in the question, because I assumed it wasn't relevant).
这篇关于H2中的递归CTE:未返回任何数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!