473,326 Members | 2,133 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,326 software developers and data experts.

Sql-Translation: Oracle to DB2

Hello,

I want to tanslate the following Oracle-SQL to DB2, but I'm stuck, any
hints?

Thank you in advance.

UPDATE TBZL0361CSFLQ_AGG CF
INNER JOIN (SELECT * FROM TBS_AGG WHERE tbs_name = CASE WHEN
CF.solve_name = 'ZAB'
THEN 'BII' ELSE 'STD' END) AS TB1 ON (CF.eventdate = TB1.tbs_date)
INNER JOIN (SELECT * FROM TBS_AGG WHERE tbs_name = CASE WHEN
CF.solve_name = 'ZAB'
THEN 'BII' ELSE '180D' END) AS TB2 ON (CF.eventdate = TB2.tbs_date)
SET
(CF.TBS1_Name,
CF.TBS1_Start,
CF.TBS1_End,
CF.TBS2_Name,
CF.TBS2_Start,
CF.TBS2_End) =
(SELECT
CASE WHEN CF.solve_name = 'ZAB' THEN 'BII' ELSE 'STD' END,
CASE WHEN CF.solve_name = 'ZAB' THEN NVL(tb1.tbs_start, (SELECT
MAX(tbs_start) FROM TBS_AGG WHERE tbs_name = 'BII')) ELSE
NVL(tb1.tbs_start, (SELECT MAX(tbs_start) FROM TBS_AGG WHERE tbs_name
= 'STD')) END,
CASE WHEN CF.solve_name = 'ZAB' THEN NVL(tb1.tbs_start, (SELECT
MAX(tbs_start) FROM TBS_AGG WHERE tbs_name = 'BII')) ELSE
NVL(tb1.tbs_end, (SELECT MAX(tbs_end) FROM TBS_AGG WHERE tbs_name =
'STD')) END,
CASE WHEN CF.solve_name = 'ZAB' THEN NULL ELSE '180D' END,
CASE WHEN CF.solve_name = 'ZAB' THEN NULL ELSE NVL(tb2.tbs_start,
(SELECT MAX(tbs_start) FROM TBS_AGG WHERE tbs_name = '180D')) END,
CASE WHEN CF.solve_name = 'ZAB' THEN NULL ELSE NVL(tb2.tbs_end,
(SELECT MAX(tbs_end) FROM TBS_AGG WHERE tbs_name = '180D')) END
)
WHERE
korrektur_kz='M'

Best regards,
Sascha

Aug 8 '07 #1
5 4502
Without endorsing the statement as such...
(it is rather ugly and there must be a much more efficient solution):

MERGE INTO TBZL0361CSFLQ_AGG CF
USING (SELECT TB1.tbs_start AS tbs1_start,
TB2.tbs_end AS tbs1_end,
TB1.tbs_name AS tbs1_name,
TB1.tbs_start AS tbs1_start,
TB2.tbs_end AS tbs1_end,
TB1.tbs_name AS tbs1_name,
TB1.tbs_date
FROM TBS_AGG AS TB1
INNER JOIN TBS_AGG AS TB2
ON TB1.tbs_date = TB2.tbs_date
WHERE tbs_name = CASE WHEN CF.solve_name = 'ZAB'
THEN 'BII' ELSE 'STD' END
AND tbs_name = CASE WHEN CF.solve_name = 'ZAB'
THEN 'BII' ELSE '180D' END) AS TBS
ON CF.eventdate = tbs_date
AND korrektur_kz='M'
WHEN MATCHED THEN UPDATE
SET
(CF.TBS1_Name,
CF.TBS1_Start,
CF.TBS1_End,
CF.TBS2_Name,
CF.TBS2_Start,
CF.TBS2_End) =
(SELECT CASE WHEN CF.solve_name = 'ZAB' THEN 'BII' ELSE 'STD' END,
CASE WHEN CF.solve_name = 'ZAB'
THEN COALECSE(tbs1_start, (SELECT MAX(tbs_start) FROM
TBS_AGG WHERE tbs_name = 'BII'))
ELSE COALESCE(tbs1_start, (SELECT MAX(tbs_start) FROM
TBS_AGG WHERE tbs_name = 'STD'))
END,
CASE WHEN CF.solve_name = 'ZAB'
THEN COALESCE(tbs1_start, (SELECT MAX(tbs_start) FROM
TBS_AGG WHERE tbs_name = 'BII'))
ELSE COALESCE(tbs1_end, (SELECT MAX(tbs_end) FROM TBS_AGG
WHERE tbs_name = 'STD'))
END,
CASE WHEN CF.solve_name = 'ZAB' THEN NULL ELSE '180D' END,
CASE WHEN CF.solve_name = 'ZAB' THEN NULL
ELSE COALESCE(tbs1_start, (SELECT MAX(tbs_start) FROM
TBS_AGG WHERE tbs_name = '180D'))
END,
CASE WHEN CF.solve_name = 'ZAB' THEN NULL
ELSE COALESCE(tbs2_end, (SELECT MAX(tbs_end) FROM TBS_AGG
WHERE tbs_name = '180D'))
END
)
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Aug 8 '07 #2
Do use a merge statement with a sql inside it that returns a table
like..

merge into table TBZL0361CSFLQ_AGG CF
using
(
SELECT * FROM TBS_AGG WHERE.......)

This will update the table using the primary keys to refer the rows.
>From clause should not occur in an update clause, according to SQL
standard
and DB2 enforces it, Oracle doesnt.. this is one of many reasons I
dont like
Oracle..

Aug 8 '07 #3
Sa***************@gmail.com wrote:
Hello,

I want to tanslate the following Oracle-SQL to DB2, but I'm stuck, any
hints?

Thank you in advance.

UPDATE TBZL0361CSFLQ_AGG CF
INNER JOIN (SELECT * FROM TBS_AGG WHERE tbs_name = CASE WHEN
CF.solve_name = 'ZAB'
THEN 'BII' ELSE 'STD' END) AS TB1 ON (CF.eventdate = TB1.tbs_date)
INNER JOIN (SELECT * FROM TBS_AGG WHERE tbs_name = CASE WHEN
CF.solve_name = 'ZAB'
THEN 'BII' ELSE '180D' END) AS TB2 ON (CF.eventdate = TB2.tbs_date)
SET
(CF.TBS1_Name,
CF.TBS1_Start,
CF.TBS1_End,
CF.TBS2_Name,
CF.TBS2_Start,
CF.TBS2_End) =
(SELECT
CASE WHEN CF.solve_name = 'ZAB' THEN 'BII' ELSE 'STD' END,
CASE WHEN CF.solve_name = 'ZAB' THEN NVL(tb1.tbs_start, (SELECT
MAX(tbs_start) FROM TBS_AGG WHERE tbs_name = 'BII')) ELSE
NVL(tb1.tbs_start, (SELECT MAX(tbs_start) FROM TBS_AGG WHERE tbs_name
= 'STD')) END,
CASE WHEN CF.solve_name = 'ZAB' THEN NVL(tb1.tbs_start, (SELECT
MAX(tbs_start) FROM TBS_AGG WHERE tbs_name = 'BII')) ELSE
NVL(tb1.tbs_end, (SELECT MAX(tbs_end) FROM TBS_AGG WHERE tbs_name =
'STD')) END,
CASE WHEN CF.solve_name = 'ZAB' THEN NULL ELSE '180D' END,
CASE WHEN CF.solve_name = 'ZAB' THEN NULL ELSE NVL(tb2.tbs_start,
(SELECT MAX(tbs_start) FROM TBS_AGG WHERE tbs_name = '180D')) END,
CASE WHEN CF.solve_name = 'ZAB' THEN NULL ELSE NVL(tb2.tbs_end,
(SELECT MAX(tbs_end) FROM TBS_AGG WHERE tbs_name = '180D')) END
)
WHERE
korrektur_kz='M'

Best regards,
Sascha
I wouldn't recommend doing this in Oracle either. There are far more
efficient ways.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu (replace x with u to respond)
Aug 8 '07 #4
On Aug 8, 5:59 pm, Serge Rielau <srie...@ca.ibm.comwrote:
Without endorsing the statement as such...
(it is rather ugly and there must be a much more efficient solution):
......

Hello,

I used your statement, but db2 reponds with error:

[IBM][CLI Driver][DB2/AIX64] SQL0104N Auf "'STD' END"
folgte das unerwartete Token ")". Zu den möglichen Token gehören:
"<table_expr>". SQLSTATE=42601

There seems to be a problem with the last pasrt of the statement, db2
expects a table and a from statement.

Regards,
Sascha
Aug 9 '07 #5
Sa***************@gmail.com wrote:
On Aug 8, 5:59 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>Without endorsing the statement as such...
(it is rather ugly and there must be a much more efficient solution):
.....

Hello,

I used your statement, but db2 reponds with error:

[IBM][CLI Driver][DB2/AIX64] SQL0104N Auf "'STD' END"
folgte das unerwartete Token ")". Zu den möglichen Token gehören:
"<table_expr>". SQLSTATE=42601
Actually it appears the error was already introduced in your own
statement (?)
You have:
SET ( ) = (SELECT CASE.., CASE, CASE..)
Remove the SELECT keyword.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Aug 9 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Dagwood | last post by:
Good morning: At least it's morning where I am. :) I have a rather newbie question I'm afraid. I have VisualStudio.NET, and have installed it along with SQL server. However I can't seem to...
2
by: Ken Lindner | last post by:
I have a need to become familiar with SQL Server 2000 for work. Needless to say I am new to SQL Server any version, but not IT in general. My employer has provided me with the SQL Server 2000...
6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
2
by: Yves Touze | last post by:
Hi All, I'm trying to migrate from SQL Server 7.0 to SQL Server 2000. I've got some ASP page which call VB components that retrieve shaped recordsets from SQL Server using the MSDATASHAPE...
9
by: Grim Reaper | last post by:
My work let me put SQL Server 7.0 Enterprise Edition on my laptop. I have never setup a server from the beginning, so I am a little new at creating server groups. Alright, I am trying to create...
11
by: Mark Yudkin | last post by:
The documentation is unclear (at least to me) on the permissibility of accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP / 2003, with separate transactions scope, from separate...
4
by: coosa | last post by:
Hi, I was installing SQL Server on my machine and during installation my PC freezed. It happens frequently on my machine. So i tried after restarting to install it again and since then i always...
1
by: Peter | last post by:
I've purchased VS.NET 2005 Standard and have tried to install SQL Server 2005 Express, but get the following error in the error log. Please could someone help me.... Microsoft SQL Server 2005...
6
by: Fuzzydave | last post by:
I am back developing futher our Python/CGI based web application run by a Postgres DB and as per usual I am having some issues. It Involves a lot of Legacy code. All the actual SQL Querys are...
14
by: Developer | last post by:
Hello All, i have recently installed VS2005 and was trying to install SQL sever 2000. I have Win XP' SP2. But when I tried installing, it only installed client tools and not the database. Can...
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.