教你快速掌握Oracle SQL到DB2 SQL的移植 （2）

     来源：赛迪网    作者：liuhe

2、Oracle中的Start with...Connect By递归查询

DB2解决方案：用with公共递归表达式来解决。

DB2解决方案：用case条件表达式完成。

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) as 

(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、Oracle中的dual表对应DB2中的SYSIBM.SYSDUMMY1表

DB2解决方案：对应于DB2中的 SYSIBM.SYSDUMMY1表

Oracle SQL： 

------------------------- 

select 15 as ttt from dual 

结果： 

ttt 

------- 

15 



DB2 SQL： 

------------------------- 

select 15 as ttt from SYSIBM.SYSDUMMY1 

结果： 

ttt 

------- 

15

4、日期转换问题

DB2解决方案：有相应的函数

Oracle SQL： 

------------------------- 

select m.* 

from dj_mcdj m 

where m.mcqc || '' '' like ''%$P%'' 

and m.xzqhdm || '' '' like ''%$P%'' 

and m.hylbdm || '' '' like ''%$P%'' 

and m.blqsrq >= to_date(''$P'', ''yyyy-mm-dd'') 

and m.blqsrq < to_date(''$P'', ''yyyy-mm-dd'')+1 

DB2 SQL： 

select m.* 

from dj_mcdj m 

where m.mcqc || '' '' like ''%%'' 

and m.xzqhdm || '' '' like ''%%%'' 

and m.hylbdm || '' '' like ''%%%'' 

and date(m.blqsrq) >= date(''1900-01-01'') 

and date(m.blqsrq) < date(''2050-01-01'')+1 day