473,231 Members | 1,758 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

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

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
5 17442
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

"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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Thierry B. | last post by:
Hi, from SQL*Plus, i use: DESCRIBE MyTable and I get this result (example): Nom NULL ? Type ----------------------------------------- --------...
0
by: JONL | last post by:
Have been connecting to oracle using the following with success my $dbHandle = DBI->connect($sConnectString, $sUsername, $sPassword ); adding ,\%attr after password permits me to set the db handle...
4
by: susmita_ganguly | last post by:
Hi I am trying to upgrade from oracle 8i to oracle 9i on the same server ..I don't know much abt migration . Can anyone help me out. Thanks. Susmita
4
by: Joseph Gulla | last post by:
Hi all I'm trying to install the OEM for 9i. The install goes smoothly. I can sql connect to db with repository when I use Oracle enterprise manger consol login I can connect standalone. The...
4
by: Amardeep Verma | last post by:
Hi, I have a quick question. Which role/privileges are required before a user can give the statement "GRANT ALL PRIVILEGES"? Thanking you in Advance Have a nice day
4
by: Tig | last post by:
Hi all. I have a need to connect to an Oracle 7.3.3.5 database. I have a user who successfully connects to it with her Oracle 7.3 client. I have an Oracle 9.2 client installed on my machine....
4
by: thiamwah | last post by:
Hi, I have a SQL statement that access to one database (DB2) i.e. SELECT DB1.TABLE1.TABLE1_ID, DB1.TABLE1.FIELD_A, DB1.TABLE2.FIELD_1, DB1.TABLE2.FIELD_2,
5
by: Steven C | last post by:
Hello! I am trying to connect to a SQL Server (MSDE) database in mixed mode authentication, via C#, but when I use the MSDE instance name, I keep getting an "Unrecognized Escape Sequence"...
4
by: musai | last post by:
I have created vb oracle application I installed oracle client in three machine. All machine was reconfigured after oracle installed I could see table and record set through sql plus sheet in...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.