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

[Michael]

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

[Serge Rielau]

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

[Rhino]

"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

[Michael]

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

[Serge Rielau]

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

[Michael]

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