一则 > ORA-01722: invalid number错误的处理,原因:substr截取的字段中有空格
--出错的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
本文来自博客园,作者:IT情深,转载请注明原文链接:https://www.cnblogs.com/wh445306/p/16751880.html