db2转Oracle规则,DB2 转Oracle

DB2 转Oracle

某天是星期几的函数

DB2:

select a.yyxe,a.zblb,a.ysdm,a.daynum,b.zjxm  from zy_yspb a ,zy_zjlb b where a.ysdm=b.id and  a.ksdm=10000 order by abs( a.daynum -DAYOFWEEK(current   date)+1) desc,a.daynum,a.zblb

sql.executeQuery:ORA-00936: 缺失表达式

Oracle :

select a.yyxe,a.zblb,a.ysdm,a.daynum,b.zjxm  from zy_yspb a ,zy_zjlb b where a.ysdm=b.id and  a.ksdm=10000 order by abs( a.daynum -to_char(sysdate,'D')+1) desc,a.daynum,a.zblb

to_char 和to_date的用法

DB2:

select count(*) as yyrs from zy_zzd where zblb=2 and zzys=10000 and zzrq >='2009-08-21 00:00:00' and zzrq <='2009-08-21 23:59:59' and -DAYOFWEEK(current   date)-1=2

Oracle :

select count(*) as yyrs from zy_zzd where zblb=2 and zzys=10000 and zzrq >=to_date('2009-08-21 00:00:00','yyyy-mm-dd hh24:mi:ss')  and zzrq <=to_date('2009-08-21 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_char(zzrq,'D')-1=2

ZCJGMC，ZRJGMC，SHYSMC，ZRKSMC，CARDID，ZCKSMC