在使用nvl()函数处理空值问题的时候,明明第一个表达式是有值的并且两个做除法的参数都做了空值处理但是却一直返回0。仔细检查后发现问题所在
原SQL如下:
select 'Total' as div,nvl(newemp,0) as newemp,nvl(forms,0) as forms,nvl(accnum,0) as accnum,nvl(lag,0) as lag, nvl(1 - lag/forms, 0) as perc
from (XXX) as accnum, (XXX) as newemp, (XXX) as forms, (XXX) as lag
1、参数名,别名和表名一致,导致一直以为我是用的处理后的别名去做除法
2、发现后,更换别名去除,报错,提示这是未定义的
3、在除的时候再分别对两个参数做空值处理就好了
更改后如下:
select 'Total' as div,nvl(newemp,0) as newemp,nvl(forms,0) as forms,nvl(accnum,0) as accnum,nvl(lag,0) as lag, (1 - (nvl(lag,0)/nvl(forms,0))) as perc
from (XXX) as accnum, (XXX) as newemp, (XXX) as forms, (XXX) as lag
总结:还是因为第一个表达式的值为null才导致返回的是0