难得一遇的oracle字符转换number型！！

[QQ359120087]

sql 语句： select jh,dwdm,to_char(tjrq,'yyyy-mm')as tjrq,yxcl,tjsj,kjsj,
to_number(
case when (kjsj>tjsj and kjrq>tjrq) then (kjrq-tjrq)||'.'||(kjsj-tjsj)
when (kjsj>tjsj and kjrq=tjrq) then replace(substr(0||'.'||(kjsj- tjsj),1,4),'..','.')
when kjsj=tjsj then (kjrq-tjrq)||''
when kjsj<tjsj then (kjrq-tjrq-1)||'.'||(24-(tjsj-kjsj))
end) tksj
from sc_Fx_05 结果：ora-01772 invalid number
改为
select jh,dwdm,to_char(tjrq,'yyyy-mm')as tjrq,yxcl,tjsj,kjsj,
to_number(
case when (kjsj>tjsj and kjrq>tjrq) then (kjrq-tjrq)||'.'||(kjsj-tjsj)
when kjsj=tjsj then (kjrq-tjrq)||''
when kjsj<tjsj then (kjrq-tjrq-1)||'.'||(24-(tjsj-kjsj))
end) tksj
from sc_Fx_05 结果ok
问题：在字符串进行replace之后 在to_number 出错，请高手指点！

[YY_MM_DD]

case when (kjsj>tjsj and kjrq>tjrq) then (kjrq-tjrq)||'.'||(kjsj-tjsj)
when (kjsj>tjsj and kjrq=tjrq) then replace(substr(0||'.'||(kjsj- tjsj),1,4),'..','.')
when kjsj=tjsj then (kjrq-tjrq)||''
when kjsj <tjsj then (kjrq-tjrq-1)||'.'||(24-(tjsj-kjsj))
end

这个输出的是什么???
贴出来看看..

[suiziguo]

能否给下sc_Fx_05建表语句？

[YY_MM_DD]

还有个问题是...如果你的条件都不符合你的when时候,就会出错..
最好写个default

[QQ359120087]

JH DWDM TJRQ YXCL TJSJ KJSJ TKSJ
------------ ------------ ------- ---------- ------ ------ ----------
GLL1N271 30200687 2006-09 0.10 17.30 18.30
GLL1-682 30200687 2006-09 8.00 8.00 2
YAA68-3 30200716 2006-09 0.20 13.00 14.00
YAA66P2 30200716 2006-09 0.30 8.30 9.30
DXX50X63 30200659 2006-09 28.20 8.00 9.00 30.1

[QQ359120087]

计算出来的tksj默认为字符型的

[QQ359120087]

when捕捉了所有条件，就是replace之后在执行to_number报错
tjrq kjrq 为date tjsj 、kjsj 为number

[suiziguo]

方便的话建表DDL语句给下。

实地调试下

[QQ359120087]

create table SC_FX_05
(
JH VARCHAR2(12) not null,
TJRQ DATE not null,
DWDM VARCHAR2(12),
DYDM VARCHAR2(6),
CYFS VARCHAR2(3),
GJLB VARCHAR2(4),
GJLB1 VARCHAR2(4),
KJRQ DATE,
TJSJ NUMBER(4,2),
DJBJ VARCHAR2(10),
TJBJ VARCHAR2(10),
ZFCS VARCHAR2(200),
YXCL NUMBER(8,2),
BZ VARCHAR2(100),
KJSJ NUMBER(4,2),
TCQ NUMBER(12,6),
CTJBZ NUMBER(1),
TJCS NUMBER(1) not null,
SCZYSJ DATE,
ZYKJFS VARCHAR2(4),
ZYKGRQ DATE,
SCCS VARCHAR2(30),
RCYL1 NUMBER(6,1),
RCYL NUMBER(6,1),
HS NUMBER(5,1),
DYM NUMBER(6,1),
BS NUMBER(7,2),
XBCS VARCHAR2(200),
YZY NUMBER(8,2),
CSYJ VARCHAR2(200),
DWDM3 VARCHAR2(12),
BJ NUMBER(3),
PL NUMBER(5,1),
ZYKGSJ NUMBER(4,2),
JJRQ DATE,
JJSJ NUMBER(4,2)
) 表结构，

[小灰狼W]

begin
for cur in (select replace(substr(0||'.'||(kjsj-tjsj),1,4),'..','.') a from sc_fx_05
where kjsj>tjsj and kjrq=tjrq)
loop
declare
v number;
begin
v:=to_number(cur.a);
exception
when others then
dbms_output.put_line(cur.a);
end;
end loop;
end;

[suiziguo]

试过了，空表运行该语句没问题（ORACLE 10.2中）


你上面说了，tksj为字符型的，检查其中是否有to_number不合法数据？

[QQ359120087]

引用 9 楼 wildwave 的回复:

begin
for cur in (select replace(substr(0||'.'||(kjsj-tjsj),1,4),'..','.') a from sc_fx_05
  where kjsj>tjsj and kjrq=tjrq)
loop
declare
v number;
begin
v:=to_number(cur.a);
exception
when others then
dbms_output.put_line(cur.a);
end;
end loop;
end;

执行在一条sql语句，问题就在于replace替换在进行转换出错，去掉那一行就ok，添上就错，问题点在这，很感谢大家！

[小灰狼W]

引用 11 楼 qq359120087 的回复:

引用 9 楼 wildwave 的回复:begin for cur in (select replace(substr(0||'.'||(kjsj-tjsj),1,4),'..','.') a from sc_fx_05   where kjsj>tjsj and kjrq=tjrq) loop declare v number; begin v:=to_number(cur.a); except?-

问题在replace这里，上面写的过程应该可以把出错的地方找出来，试试

[QQ359120087]

引用 10 楼 suiziguo 的回复:

试过了，空表运行该语句没问题（ORACLE 10.2中）


你上面说了，tksj为字符型的，检查其中是否有to_number不合法数据？

谢谢你提醒了我，因为数据库存有几百万条，只大略的看了看， 现在仔细看就是有不合法的数据，谢谢 suiziguo！