从Oracle到Derby-ConnectBy并以Derby中的同等功能开始 [英] Oracle to Derby - ConnectBy and Start With Equivalent in Derby

问题描述

以下Query1适用于Oracle。我希望在Derby中执行相同的功能。我得到了Query2，作为这里要遵循的建议之一。但这仍然会引发Error1。请有人帮我摆脱这个问题。我该怎么办？

The following Query1 is fine with Oracle. I want the same functionality to be carried out in Derby. I got the Query2, as one of the suggestion to be followed here. But still that is throwing the Error1. Please somebody help me out to get rid of this. What should I do?

查询1：

Select f.folder_id, f.identifier

            From FOLDER f, STOREENT se
            Where f.type = 'AttributeFolder'
            And se.storeent_id = f.storeent_id 
            And se.identifier = 'Global_CAS'
            And f.identifier = 10051 
connect by prior f.folder_id = f.parentfolder_id
start with f.identifier = 'Categories Descriptors [Global.B2C.Sales]'

Query2：

with cte as (

select folder_id, f.identifier, cast(null as varchar(255)) parentfolder_id,

       0 as depth,
       se.identifier as se_identifier
       from folder f
       join storeent se on se.storeent_id = f.storeent_id
       where f.identifier = 'A'
    union all
    select f.folder_id, f.identifier, f.parentfolder_id,
       cte.depth + 1 as depth,
       se.identifier as se_identifier
    from folder f
    join storeent se on se.storeent_id = f.storeent_id
    join cte cte on f.parentfolder_id = cte.folder_id
)
Select parentfolder_id, folder_id, identifier, depth
from cte

错误1：

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "with" at line 1, column 1

推荐答案

当前，derby不支持递归查询，请参见 https://issues.apache.org/jira/browse/DERBY-11 （于2004年开放，最后评论于2014年）。因此，不幸的是，您无法在单个查询中执行此操作，您必须自己编写某种树遍历循环。

Currently, derby does not support recursive queries, see https://issues.apache.org/jira/browse/DERBY-11 (openened in 2004, last comment in 2014). So unfortunately you cannot do this in a single query, you have to write some kind of tree traversal loop yourself.

这篇关于从Oracle到Derby-ConnectBy并以Derby中的同等功能开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！