Oracle to Derby - ConnectBy and Start With Equival

Question

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?

Query1:

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

Error1:

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

Answer 1

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.