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

Sql-Translation: Oracle to DB2

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.