--出错的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 lower3