一则 > 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