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 出错,请高手指点!
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 出错,请高手指点!
解决方案 »
- Oracle 9i数据恢复,请进!
- 问一下关于Clob的问题,20分奉送别嫌少!
- ORACLE数据登陆与权限问题
- IDE(Myeclipse)连不上oracle,sqlplus能连上?不知道怎么搞啊?
- 如何建立新的预编译项目文件呢
- Oracle 简单问题
- 关于在前台程序里面建ORACLE的JOB???高手请进
- 我的服务器受攻击了!!
- 如何使用long型?能不能用long like '%字符串%'?如果不能,有没有其他方法解决,谢谢!
- 我下载了doad6(direct oracle access for delphi 6),非常爽,但不知道有没有后遗症?
- 关于oracle10g 引用jdk1.5 jar包中的方法问题
- 关于oracle的查询问题
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这个输出的是什么???
贴出来看看..
最好写个default
------------ ------------ ------- ---------- ------ ------ ----------
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
tjrq kjrq 为date tjsj 、kjsj 为number
(
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)
) 表结构,
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;
你上面说了,tksj为字符型的,检查其中是否有to_number不合法数据?