SEO

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

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

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..

Sascha.Moellering@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
damorgan@x.washington.edu (replace x with u to respond)

On Aug 8, 5:59 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> 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

Sascha.Moellering@gmail.com wrote:
> On Aug 8, 5:59 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
>> 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