By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,004 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,004 IT Pros & Developers. It's quick & easy.

How to convert this Oracle SQL(CONNECT BY) to DB2 SQL

P: n/a
Hi,

I am migrating an application from Oracle to DB2. There is an Oracle SQL like:

SELECT c1, c2
FROM table
CONNECT BY c2 = PRIOR c1
START WITH c2 IN ('1','2')

My question is how to convert above SQL to DB2 SQL.

Thanks in advance,
Michael
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Here is the SQL Standard version (also supported by SQL Server 2005)
which works in DB2 for LUW
WITH rec(c1, c2)
AS (SELECT c1, c2 FROM table WHERE c2 IN ('1','2')
UNION ALL
SELECT table.c1, table.c2 FROM table, rec WHERE table.c2 = rec.c2)
SELECT c1, c2 FROM rec

Cheers
Serge
Nov 12 '05 #2

P: n/a

"Michael" <mi**********@rogers.com> wrote in message
news:8f**************************@posting.google.c om...
Hi,

I am migrating an application from Oracle to DB2. There is an Oracle SQL like:
SELECT c1, c2
FROM table
CONNECT BY c2 = PRIOR c1
START WITH c2 IN ('1','2')

My question is how to convert above SQL to DB2 SQL.

Can you point us to an SQL Reference manual for Oracle? I've never seen a
statement like this in DB2 but if I can see an Oracle SQL Reference I might
be able to figure out a DB2 SQL equivalent, if there is one.

Rhino
Nov 12 '05 #3

P: n/a
The sql you provided works. But when I put it into the following sql
as a subquery, i alwasy got error message:

SQL0104N An unexpected token "AS" was found

Is there limitation for DB2 WITH...AS... clause?

SELECT
T.a,
T.b,
(WITH rec(c1, c2)
AS (SELECT c1, c2 FROM table WHERE c2 = T.a
UNION ALL
SELECT table.c1, table.c2 FROM table, rec WHERE table.c2 =
rec.c2)
SELECT c1, c2 FROM rec FETCH FIRST 1 ROWS ONLY
) as c
FROM T
thanks,
Michael
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2t*************@uni-berlin.de>...
Here is the SQL Standard version (also supported by SQL Server 2005)
which works in DB2 for LUW
WITH rec(c1, c2)
AS (SELECT c1, c2 FROM table WHERE c2 IN ('1','2')
UNION ALL
SELECT table.c1, table.c2 FROM table, rec WHERE table.c2 = rec.c2)
SELECT c1, c2 FROM rec

Cheers
Serge

Nov 12 '05 #4

P: n/a
WITH in DB2 is only support at the top level of SELECT,
In the SELECT feeding an INSERT and in the RETURN statement of SQL
functions.

Often you can pull the WITH up to top level.
In your case we need a little trick though due to the correlation:

CREATE FUNCTION rec(a INT)
RETURNS TABLE(c1 INT, c2 INT)
RETURN
SELECT WITH rec(c1, c2)
AS (SELECT c1, c2 FROM table WHERE c2 = T.a
UNION ALL
SELECT table.c1, table.c2 FROM table, rec
WHERE table.c2 = rec.c2)
SELECT c1, c2 FROM rec;

T.a,
T.b,
(SELECT c1, c2 FROM TABLE(rec(T.a)) AS c FETCH FIRST ROW ONLY
) as c
FROM T;

Am I corrcet to assuem that this is a simplified example?
The FETCH FIRST seems rather bizarre semantically unless you presume
some sort of order (which DB2 does not!)

Cheers
Serge
Nov 12 '05 #5

P: n/a
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2t*************@uni-berlin.de>...
WITH in DB2 is only support at the top level of SELECT,
In the SELECT feeding an INSERT and in the RETURN statement of SQL
functions.

Often you can pull the WITH up to top level.
In your case we need a little trick though due to the correlation:

CREATE FUNCTION rec(a INT)
RETURNS TABLE(c1 INT, c2 INT)
RETURN
SELECT WITH rec(c1, c2)
AS (SELECT c1, c2 FROM table WHERE c2 = T.a
UNION ALL
SELECT table.c1, table.c2 FROM table, rec
WHERE table.c2 = rec.c2)
SELECT c1, c2 FROM rec;

T.a,
T.b,
(SELECT c1, c2 FROM TABLE(rec(T.a)) AS c FETCH FIRST ROW ONLY
) as c
FROM T;

Am I corrcet to assuem that this is a simplified example?
The FETCH FIRST seems rather bizarre semantically unless you presume
some sort of order (which DB2 does not!)

Cheers
Serge


It's simplified example. You answer makes a lot of sense!
Thank you,
Michael
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.