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

Question

  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 出错，请高手指点！

Answer 1

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这个输出的是什么???
贴出来看看..

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

方便的话建表DDL语句给下。实地调试下

Answer 7

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)
) 表结构，

Answer 8

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;

Answer 9

试过了，空表运行该语句没问题（ORACLE 10.2中）
你上面说了，tksj为字符型的，检查其中是否有to_number不合法数据？

Answer 10

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

Answer 11

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

Answer 12

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