一则 > ORA-01722: invalid number错误的处理,原因:substr截取的字段中有空格
2023-09-11 14:15:13 时间
--出错的SQL语句位置(其实本身SQL语句无问题)问题出在bornDate字段中有值为空格导致错误
sum(case when to_char(sysdate,'yyyy') - substr(bornDate,1,4)<30 then 1 else 0 end ) as lower30,
--正确的写法,Trim(bornDate)
sum(case when to_char(sysdate,'yyyy') - substr(Trim(bornDate),1,4)<30 then 1 else 0 end ) as lower30,
--排查问题步骤:
--OK的
select to_char(sysdate,'yyyy') -substr('1975-10-09',1,4) as count from dual --44年
select case when (to_char(sysdate,'yyyy') - substr('1995-10-09',1,4))<30 then 1 else 0 end as lower30 from dual
select sum(case when (to_char(sysdate,'yyyy') - substr('1995-10-09',1,4))<30 then 1 else 0 end ) as lower30 from k_micfo
select bornDate,loginid from k_micfo where not regexp_like(bornDate,'^[0-9]|[-]$');
select sum(case when (to_char(sysdate,'yyyy') - substr(Trim(bornDate), 1, 4))<30 then 1 else 0 end ) as lower30 from k_micfo
--sql server 原SQL语句:
select b.iyear,d.CNum as Scale,d.Address,d.Approval,d.OfficeName,
(convert(numeric(18),case isnull(d.employees,'') when '' then 0 else d.employees end ) + pencon ) as employees ,
convert( varchar ,(convert(numeric(18,2), (CAST(b.totalincome AS money) /10000)))) as totalincome ,
case when CAST(isnull(c.totalincome,0) AS money) = 0 then 0 else
(CAST(isnull(b.totalincome, 0) AS money)-CAST(isnull(c.totalincome, 0) AS money))/CAST(isnull(c.totalincome,0) AS money) end as ZZL,a.*
from (
select officecode,count(loginid) as pencon,
sum(case when datediff(year,bornDate,getDate())<30 then 1 else 0 end) as lower30,
sum(case when datediff(year,bornDate,getDate())>=30 and datediff(year,bornDate,getDate())<=50 then 1 else 0 end) as in30T50,
sum(case when datediff(year,bornDate,getDate())>50 then 1 else 0 end) as upper50,
sum(case when Diploma='大专' then 1 else 0 end ) as DZ,
sum(case when Diploma='本科' then 1 else 0 end ) as BK,
sum(case when Diploma='硕士' or Diploma='博士' then 1 else 0 end ) as SSORBS,
sum(case when Diploma='其他' or Diploma='' or Diploma is null or Diploma='中专' or Diploma='高中' or Diploma='初中' then 1 else 0 end ) as other
from K_Micfo where State=0 and officecode is not null and officecode <>'' and officename <> '' and officename is not null group by officecode ) a
left join k_costpay b on a.officecode=b.iuser and b.iyear='2019'
left join (select iuser, totalincome,iyear from k_costpay ) c on (c.iuser=b.iuser and b.iyear=c.iyear+1)
left join k_company d on a.officeCode=d.loginid
--oracle修改后正确运行的语句:
select b.iyear,d.CNum as Scale,d.Address,d.Approval,d.OfficeName,
cast(case when nvl(d.employees,' ')=' ' then '0' else d.EMPLOYEES end as Number(18,0))+ pencon as employees ,
to_char(CAST(b.totalincome as Number(18,2)) /10000 ,'FM999,999,999,999,990.00') as totalincome ,
case when CAST(nvl(c.totalincome,0) as Number(18,2)) = 0 then 0 else (CAST(NVL(b.totalincome, 0)
as Number(18,2))-CAST(NVL(c.totalincome, 0) as Number(18,2)))/CAST(NVL(c.totalincome,0) as Number(18,2)) end as ZZL,
a.* from (
select officecode,count(loginid) as pencon,
sum(case when to_char(sysdate,'yyyy') - substr(Trim(bornDate),1,4)<30 then 1 else 0 end ) as lower30,
sum(case when to_char(sysdate,'yyyy') -substr(Trim(bornDate),1,4)>=30 and to_char(sysdate,'yyyy') -substr(Trim(bornDate),1,4)<=50 then 1 else 0 end) as in30T50,
sum(case when to_char(sysdate,'yyyy') -substr(Trim(bornDate),1,4)>50 then 1 else 0 end) as upper50,
sum(case when Diploma='大专' then 1 else 0 end ) as DZ, sum(case when Diploma='本科' then 1 else 0 end ) as BK,
sum(case when Diploma='硕士' or Diploma='博士' then 1 else 0 end ) as SSORBS,
sum(case when Diploma='其他' or Diploma='' or Diploma is null or Diploma='中专' or Diploma='高中' or Diploma='初中' then 1 else 0 end ) as other
from K_Micfo where State=0 and NVL(officecode,' ') <>' ' and NVL(officename,' ') <> ' ' group by officecode
) a
left join k_costpay b on a.officecode=b.iuser and b.iyear='2019'
left join (select iuser, totalincome,iyear from k_costpay ) c on (c.iuser=b.iuser and b.iyear=c.iyear+1)
left join k_company d on a.officeCode=d.loginid
相关文章
- Google Earth Engine(GEE)——argument ‘input‘: Invalid type. Expected type: Image<unknown bands>错误
- gee错误:Image.multiply, argument ‘image2‘: Invalid type. Expected type: Image<unknown bands>.
- 二则 ORA-01722: invalid number错误的处理,substr to_char之间的区别。连接符号错误 + ||
- 线程池运用实例——一次错误的多线程程序设计以及修复过程
- 九、Mysql - 错误日志 - 慢日志 - 通用日志 - 二进制日志 - undolog - redolog - 事务的执行过程
- 微信-js sdk invalid signature签名错误 问题解决
- 《UCD火花集2:有效的互联网产品设计 交互/信息设计 用户研究讨论》一2.3 交互设计师容易犯的错误:把自己禁锢在解决方案之中
- netdom remove 错误:netdom remove
- docker export import后,导入镜像,启动时的错误,Error response from daemon: No command specified
- 启动tomcat出现内存溢出错误 java.lang.OutOfMemoryError: PermGen space
- Linux socket编程中调用 inet_ntoa 函数产生的段错误 “Segmentation fault (core dumped)” 的原因及解决办法
- php 在服务器端开启错误日志记录方法
- delphi vlc 安装bug 处理编译错误"0" is an invalid value for the "DebugInformation" parameter of the "DCC"
- odoo15 提示错误,莫名模块xxx
- macOS 安装mtr 和mtr: Failure to start mtr-packet: Invalid argument 错误的解决