Migration solution from Oracle SQL to DB2 SQL

1. decode in cancel

DB2 solution: Use case conditional expressions.

Two syntax modes of case:

(1) CASE
WHEN condition THEN result 1
ELSE result 2
END

(2) CASE expression 1
WHEN expression 2 THEN result 1
ELSE result 2
END

The above WHEN can be repeated multiple times, just like the expression of SWITCH... CASE in C.

For example:
Select ordno, CUSNO,
Case month (SHIPDATE)
WHEN '01 'then'jan'
WHEN '02 'then' feb'
WHEN '03 'then'mar'
WHEN '04 'then' apr'
WHEN '05 'then' May'
WHEN '06 'then' jun'
WHEN '07 'then' Jul'
WHEN '08 'then' aug'
WHEN '09 'then' sep'
WHEN '10' THEN 'oct'
WHEN '11' THEN 'nov'
WHEN '12' THEN 'dec'
END
FROM FILE

Application instance:

Oracle SQL:
-------------------------
Select decode (t. organtypecode, 'D', t. parent, 's', t. parent, t. id)
From A_ORGAN t
Where t. parent = 35

DB2 SQL:
-------------------------
Select case x. organtypecode
When 'd 'then
X. parent
When's then
X. parent
Else
X. id
End
From a_Organ x
Where x. parent = 35;

2. Start with... Connect By recursive query in Oracle

DB2 solution: Use the with public recursive expression.

DB2 solution: Use case conditional expressions.

Oracle SQL:
-------------------
Select t. id
From a_organ t
Start with t. id in (select decode (t. organtypecode,
'D ',
T. parent,
'S ',
T. parent,
T. id)
From A_ORGAN
Where t. id = 35)
Connect by t. parent = prior t. id

DB2 SQL:
-------------------------
With fkk (id)
(Select o. id from a_organ o
Where o. id = 35
UNION ALL
Select case x. organtypecode
When 'd 'then x. parent
When's 'then x. parent
Else x. id
End
From FKK fk, a_organ x
Where fk. id = x. parent)
Select distinct id from FKK;

3. The dual table in Oracle corresponds to the SYSIBM. SYSDUMMY1 table in DB2.
DB2 solution: corresponding to the SYSIBM. SYSDUMMY1 table in DB2
Oracle SQL:
-------------------------
Select 15 as ttt from dual

Result:
Ttt
-------
15

DB2 SQL:
-------------------------
Select 15 as ttt from SYSIBM. SYSDUMMY1

Result:
Ttt
-------
15

4. date conversion

DB2 solution: corresponding functions

Oracle SQL:
-------------------------
Select m .*
From dj_mcj m
Where m. mcqc | ''like '% $ P {QYMC} %'
And m. xzqhdm | ''like '% $ P {XZQH} %'
And m. hylbdm | ''like '% $ P {HYLB} %'
And m. blqsrq> = to_date ('$ P {QSRQ}', 'yyyy-mm-dd ')
And m. blqsrq <to_date ('$ P {JZRQ}', 'yyyy-mm-dd') + 1

DB2 SQL:
---------------------
--------------------
-- Name: Name library Query
-- Author: Lei Zhimin
-- Date: 2006-10-27
-- FOR: DB2
--------------------
Select m .*
From dj_mcj m
Where m. mcqc | ''like '%'
And m. xzqhdm | ''like '%'
And m. hylbdm | ''like '%'
And date (m. blqsrq)> = date ('2017-01-01 ')
And date (m. blqsrq) <date ('2017-01-01 ') + 1 day

(