请解说一下这是什么原因:
这下面的代码是能运行的.select '' id,
to_char(sum(Order_MONEY), 'FM99,999,999,990.00') as Order_MONEY,
to_char(sum(Stock_up_MONEY), 'FM99,999,999,990.00') as Stock_up_MONEY,
to_char(sum(RECEIVE_MONEY), 'FM99,999,999,990.00') as RECEIVE_MONEY
from (
select *
from (select t.id as id,
t.ABBR as plat_name,
t.name as platn,
to_char(decode(b.Order_MONEY, null, '0', b.Order_MONEY),
'FM99999999990.00') as Order_MONEY,
to_char(decode(c.Stock_up_MONEY,
null,
'0',
c.Stock_up_MONEY),
'FM99999999990.00') as Stock_up_MONEY,
to_char(decode(d.RECEIVE_MONEY,
null,
'0',
d.RECEIVE_MONEY),
'FM99999999990.00') as RECEIVE_MONEY
from b, c, d, s_supply_plat t
where t.id = c.plat_id(+)
and t.id = d.plat_id(+)
and t.id = b.plat_id(+))
where 1 = 1)
=================================
下面这段是不能运行的,会报"invalid number"错误select '' id,
to_char(sum(Order_MONEY), 'FM99,999,999,990.00') as Order_MONEY,
to_char(sum(Stock_up_MONEY), 'FM99,999,999,990.00') as Stock_up_MONEY,
to_char(sum(RECEIVE_MONEY), 'FM99,999,999,990.00') as RECEIVE_MONEY
from (
select *
from (select t.id as id,
t.ABBR as plat_name,
t.name as platn,
to_char(decode(b.Order_MONEY, null, '0', b.Order_MONEY),
'FM99,999,999,990.00') as Order_MONEY,
to_char(decode(c.Stock_up_MONEY,
null,
'0',
c.Stock_up_MONEY),
'FM99,999,999,990.00') as Stock_up_MONEY,
to_char(decode(d.RECEIVE_MONEY,
null,
'0',
d.RECEIVE_MONEY),
'FM99,999,999,990.00') as RECEIVE_MONEY
from b, c, d, s_supply_plat t
where t.id = c.plat_id(+)
and t.id = d.plat_id(+)
and t.id = b.plat_id(+))
where 1 = 1)
=======================
以上两段代码的唯一不同就是from里面数值格式化字符串不同,一个是"FM99999999990.00"(用这个不会报错),一个是"FM99,999,999,990.00"(这个会报错),但这两个在最外面都不会有问题,这是为什么呢?????????请帮忙解答一下原因,
这下面的代码是能运行的.select '' id,
to_char(sum(Order_MONEY), 'FM99,999,999,990.00') as Order_MONEY,
to_char(sum(Stock_up_MONEY), 'FM99,999,999,990.00') as Stock_up_MONEY,
to_char(sum(RECEIVE_MONEY), 'FM99,999,999,990.00') as RECEIVE_MONEY
from (
select *
from (select t.id as id,
t.ABBR as plat_name,
t.name as platn,
to_char(decode(b.Order_MONEY, null, '0', b.Order_MONEY),
'FM99999999990.00') as Order_MONEY,
to_char(decode(c.Stock_up_MONEY,
null,
'0',
c.Stock_up_MONEY),
'FM99999999990.00') as Stock_up_MONEY,
to_char(decode(d.RECEIVE_MONEY,
null,
'0',
d.RECEIVE_MONEY),
'FM99999999990.00') as RECEIVE_MONEY
from b, c, d, s_supply_plat t
where t.id = c.plat_id(+)
and t.id = d.plat_id(+)
and t.id = b.plat_id(+))
where 1 = 1)
=================================
下面这段是不能运行的,会报"invalid number"错误select '' id,
to_char(sum(Order_MONEY), 'FM99,999,999,990.00') as Order_MONEY,
to_char(sum(Stock_up_MONEY), 'FM99,999,999,990.00') as Stock_up_MONEY,
to_char(sum(RECEIVE_MONEY), 'FM99,999,999,990.00') as RECEIVE_MONEY
from (
select *
from (select t.id as id,
t.ABBR as plat_name,
t.name as platn,
to_char(decode(b.Order_MONEY, null, '0', b.Order_MONEY),
'FM99,999,999,990.00') as Order_MONEY,
to_char(decode(c.Stock_up_MONEY,
null,
'0',
c.Stock_up_MONEY),
'FM99,999,999,990.00') as Stock_up_MONEY,
to_char(decode(d.RECEIVE_MONEY,
null,
'0',
d.RECEIVE_MONEY),
'FM99,999,999,990.00') as RECEIVE_MONEY
from b, c, d, s_supply_plat t
where t.id = c.plat_id(+)
and t.id = d.plat_id(+)
and t.id = b.plat_id(+))
where 1 = 1)
=======================
以上两段代码的唯一不同就是from里面数值格式化字符串不同,一个是"FM99999999990.00"(用这个不会报错),一个是"FM99,999,999,990.00"(这个会报错),但这两个在最外面都不会有问题,这是为什么呢?????????请帮忙解答一下原因,
你的聚合函数用的是sum
这个函数是对数字而言的,如果或者其一是字符型,其他都是Null才行
FM99999999990.00
用这个的话,虽然是字符型,可是里面的值是纯数字,系统会自动进行隐式转换来实行sum
但如果多个逗号,系统就没法转换成数值,自然sum()会出错
像这种写法里面的'0'改成0就可以了目前是字符格式,当然报无效数字的错啦
使用了
to_char(decode(b.Order_MONEY,null,'0', b.Order_MONEY),'FM99,999,999,990.00')as Order_MONEY,所以这里已经是转成了带,的字符串,然后最外层的sum(Order_MONEY) 当然就出错了哟你这里可以在子查询里不做格式的转换,在最外层做完统计以后,在进行to_char(sum(Order_MONEY),'FM99,999,999,990.00')的格式的转换