oracle数据数形转换db2,DB2数字类型转换成字符串类型,例:ORACLE与DB2

select 1/3 from sysibm.sysdummy1;

如果想要最初的语句1/3得到非零值。可以使用如下方法:

(1) select 1.0/3 from sysibm.sysdummy1;   ---得到小数值  结果:  --0.333333333333333333333333333333

(2) select 1/3.0 from sysibm.sysdummy1;  ----同样得到小数值  结果:  --0.3333333333333333333

(3) select cast(1 as float)/3 from sysibm.sysdummy1;    --使用cast将1转为float型,然后再才除以3.  结果:0.3333333333333333

(4) select dec(1,10,2)/3 from sysibm.sysdummy1;  ---使用dec函数将1转换为decimal(10,2),然后除以3 结果:0.33333333333333333333333

其实如果想要把2个数的商四舍五入保存两位小数,

oracle中可以直接使用round函数即可:

select round(a/b,2) from dual;

而db2中却要绕几个弯才行:需要使用

select dec(cast(a as float)/b+0.005,10,2) from sysibm.sysdummy1;   先用cast转换a为float型,然后运算,再使用+0.005作为四舍五入,然后再使用dec截取2位小数。或者:

select cast(round(cast(a as float)/b,2) as decimal(10,2)) from sysibm.sysdummy1; 先使用cast转a为float,然后运算,再使用round四舍五入取2位小数,然后使用cast转换为decimal(10,2)型。

例如:

1.Oracle数据库SQL语句转换成DB2数据库SQL语句

a.Oracle SQL语句

select *

from (select t.device_name,

count(t.device_name) as num,

to_char(round(count(t.device_name) /

(select sum(aa)

from (select count(t.device_name) as aa

from EQUIPMENT_MONITORING t

where 1 = 1

and t.device_area = 'AA区'

group by t.device_name)) * 100,

2),

'990.99') || '%' as num_percent,

sum(trunc(t.deal_time - t.occa_time) * 24 * 60 * 60) as occur

from EQUIPMENT_MONITORING t

where 1 = 1

and t.device_area = 'AA区'

group by t.device_name) v

where v.occur <> 0;

b.DB2 SQL语句

select t.device_name,

count(t.device_name) as num,

CHAR(CAST(round(cast(count(t.device_name) as float) /

(select sum(aa)

from (select count(t.device_name) as aa

from EQUIPMENT_MONITORING t

where 1 = 1

and t.device_area = 'AA区'

group by t.device_name)) * 100,

2) as decimal(3, 2))) || '%' as num_percent,

sum(dec(t.deal_time - t.occa_time) * 24 * 60 * 60) as occur

from EQUIPMENT_MONITORING t

where 1 = 1

and t.device_area = 'AA区'

group by t.device_name;

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值