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屋！