SQL显示ORA-01722: 无效数字,找不到原因！求助！
SQL语句如下：SELECT RR."area_id", CC."name", RR."area_code_sum" FROM
( SELECT SUBSTR ("CERT_CODE", 1, 4) AS "area_id",
COUNT (SUBSTR("CERT_CODE", 1, 4)) AS "area_code_sum" FROM
"tourist" T1
INNER JOIN "team" T ON T1."team_id" = T."ID"
INNER JOIN "team_send" S ON S."team_id" = T."ID"
INNER JOIN "a_area" A ON T ."src_area_id" = A."ID"
WHERE S."arrive_time"
BETWEEN TO_DATE ( '2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' )
AND TO_DATE ( '2013-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss' )
AND NVL(T."state", 0) = '262144'
AND NVL(A."foreign",0) = '0'
GROUP BY SUBSTR ("CERT_CODE", 1, 4)
ORDER BY COUNT (SUBSTR("CERT_CODE", 1, 4)) DESC ) RR
RIGHT JOIN "area_code" CC
ON RR."area_id" = CC."ID";
前50行的数据能正常显示，后面就无法显示了，错误为：ORA-01722: 无效数字 AND NVL(T."state", 0) = '262144'
AND NVL(A."foreign",0) = '0'确认一下这两个的类型，以前遇到过这种问题，类型是字符型的，里面的数据有非数字，如 month_id=201310,这种如果month_id中存放的有非数字类型的，就会报无效数据，oracle查询的时候会有一个类型自动转换
页:
[1]