此问题总结自itpub,原文链接http://www.itpub.net/873682.html
引入问题
SQL> select cashier_no,sum(substr(lpad(time,4),1,2)*60+substr(lpad(time,4),3,2)) mintus from invoice_time
where cashier 2 _no=112
group 3 by cashier_no;
CASHIER_NO MINTUS
---------- ----------
112 9117
SQL> select cashier_no,sum(substr(lpad(time,4),1,2)*60+substr(lpad(time,4),3,2)) mintus from invoice_time
2 group by cashier_no;
ERROR:
ORA-01722: invalid number
no rows selected
我想问一下,下面这条语句是那里错了??
为了分析问题,我们测试如下:
SQL> select sum(substr(lpad(5,4),1,2)*60+substr(lpad(5,4),3,2)) from dual;
select sum(substr(lpad(5,4),1,2)*60+substr(lpad(5,4),3,2)) from dual
ORA-01722: 无效数字
SQL> select sum(substr(lpad(55,4),1,2)*60+substr(lpad(55,4),3,2)) from dual;
select sum(substr(lpad(55,4),1,2)*60+substr(lpad(55,4),3,2)) from dual
ORA-01722: 无效数字
SQL> select sum(substr(lpad(555,4),1,2)*60+substr(lpad(555,4),3,2)) from dual;
SUM(SUBSTR(LPAD(555,4),1,2)*60
------------------------------
355
大家看到,在time不足3位长度,就出错了呢,那究竟是哪里的问题?让我接着做个测试。
SQL> select substr(lpad(55,4),1,2) from dual;
SUBSTR(LPAD(55,4),1,2)
----------------------
SQL> select substr(lpad(55,4),1,2)*60 from dual;
select substr(lpad(55,4),1,2)*60 from dual
ORA-01722: 无效数字
大家可以看到,原来是不足两位,在取出前两位是什么呢,肯定不是null了,验证如下
SQL> select 1 from dual where substr(lpad(55,4),1,2) is null ;
1
----------
SQL> select 1 from dual where 1=1;
1
----------
1
大家都知道lpad在默认情况下补空格的
SQL> select lpad(55,4) from dual;
LPAD(55,4)
----------
55
这样找到问题根源就知道了,可以增加处理,让lpad补'0'来处理,避免这个错误。
SQL> select sum(substr(lpad(5,4,'0'),1,2)*60+substr(lpad(5,4,'0'),3,2)) from dual;
SUM(SUBSTR(LPAD(5,4,'0'),1,2)*
------------------------------
5
如果有问题,请大家指正!
[@more@]批来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7659204/viewspace-977947/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7659204/viewspace-977947/