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
