教你快速掌握Oracle SQL到DB2 SQL的移植 ―

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; 
数据挖掘研究院
 

Oracle SQL： 

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

select 15 as ttt from dual 

结果： 

ttt 

------- 

15 



DB2 SQL： 

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

select 15 as ttt from SYSIBM.SYSDUMMY1 

结果： 

ttt 

------- 

15 数据挖掘研究院 

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 
数据挖掘研究院