教你快速掌握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