Converting Oracle Query to DB2 Query

Question

How to convert the below Oracle Query to DB2. The Query is working fine in Oracle but not in DB2.

Query:
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]'

Thanks for Ur reply. I tried the same in my env. I get the following error. Can You help where i go wrong.

Query:

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

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

Because connect by and start with are not a part of ANSI SQL standards. — Dhrubajyoti Gogoi
– Dhrubajyoti Gogoi, Commented May 27, 2014 at 11:43
you could rewrite this qry with Recursive Subquery Factoring, that is part of ANSI and should also be supported by db2 — user123664
– user123664, Commented May 27, 2014 at 12:14
As others have mentioned, connect by is a Oracle proprietary construction. However, dependent of your platform/version you might be able to enable oracle compatibility mode: db2set DB2_COMPATIBILITY_VECTOR=ORA — Lennart - Slava Ukraini
– Lennart - Slava Ukraini, Commented May 27, 2014 at 12:31

mustaccio · Accepted Answer · 2014-05-27 12:33:42Z

2

As a matter of fact, this Oracle proprietary syntax is supported by DB2 for Linux, Unix and Windows v. 9.7 and later if the Oracle compatibility mode is enabled (db2set DB2_COMPATIBILITY_VECTOR=08), as described here: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.porting.doc/doc/r0052877.html

If you are migrating an Oracle database application, you may want to enable all Oracle compatibility features, not just the CONNECT BY syntax support, by setting DB2_COMPATIBILITY_VECTOR to ORA before creating your DB2 database.

answered May 27, 2014 at 12:33

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank Schmitt · Accepted Answer · 2014-05-27 12:13:38Z

Connect by and start with are Oracle-specific. To achieve similar functionality in DB2, use a Common Table Expression (CTE) (tested with SQL server, but should work on DB2, as well):

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

The CTE consists of two parts:

the base part, where you select the root node(s)
the recursive part, where you join the base table with the CTE itself

SQL Fiddle (for SQL server)

Further reading: IBM Developerworks article on migration Oracle tree queries to recursive CTE's

Collectives™ on Stack Overflow

Converting Oracle Query to DB2 Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related