执行下段SQL,结果报错,ORA-01722无效数字
SELECT * FROM (SELECT ROWNUM R, H.* FROM (SELECT T.ESEQID, T.ATTACHNAME, T.INFOCODE FROM NEWSADMIN.ANN_BASINFO T WHERE T.ATTACHTYPE = 0 AND T.NOTICEDATE >= TO_DATE('2013-04-09', 'YYYY-MM-DD') AND T.ESEQID > 4975226049 AND t.sourcename <> '发行人网站' ORDER BY T.ESEQID ASC) H ) W WHERE W.R between 1 and 50
我就很郁闷,调整成w.r > 50就可以了,一直在查rownum的原因,最后发现是隐式转换问题
T.ATTACHTYPE 字段是字符类型,执行计划中进行了to_Number()操作,所以这个字段存储了不可to_number的值,导致报错。至于为什么w.r >50 不报错,是因为plsql取了缓存又或者没取到那个出错的值。正确的如下
SELECT * FROM (SELECT ROWNUM R, H.* FROM (SELECT T.ESEQID, T.ATTACHNAME, T.INFOCODE FROM NEWSADMIN.ANN_BASINFO T WHERE T.ATTACHTYPE = '0' AND T.NOTICEDATE >= TO_DATE('2013-04-09', 'YYYY-MM-DD') AND T.ESEQID > 4975226049 AND t.sourcename <> '发行人网站' ORDER BY T.ESEQID ASC) H ) W WHERE W.R between 1 and 50
此外,如果不使用order by排序应该考虑将rownum between 1 and 50 写在里面变成如下这样,效率很高
SELECT * FROM (SELECT ROWNUM R, H.* FROM (SELECT T.ESEQID, T.ATTACHNAME, T.INFOCODE FROM NEWSADMIN.ANN_BASINFO T WHERE T.ATTACHTYPE = 0 AND T.NOTICEDATE >= TO_DATE('2013-04-09', 'YYYY-MM-DD') AND T.ESEQID > 4975226049 AND t.sourcename <> '发行人网站' and rownum between 1 and 50 ) H ) W WHERE W.R > 1