ORA-01722 - 无效号码

Question

>我在包抛出错误中有一个查询

ORA-01722：无效号码。

我尝试了很多方法，但没有运气。

select h.column  
from table1 h,table2 c 
where c.created_date='17-MAY-17' 
and nvl(c.acct_nmbr,0)=nvl(to_number(h.v_acct_num),0) 

• c.acct_nmbr是NUMBER(19,0)
• h.v_acct_num是varchar2(4000 byte)

请向我建议解决方案

Answer 1

显然v_acct_num列包含非数字值。to_number()函数有限制，即参数中的值不应包含任何非数字字符，否则系统会使用ORA-01722 : invalid number引发。如果您确定提取非数字字符时数据匹配，请在join的on子句的排序规则中使用regexp_replace(t2.v_acct_num,'[^[:digit:]]')：

select t2.column  
from table1 t1
join table2 t2 on nvl(t1.acct_nmbr,0) = nvl(regexp_replace(t2.v_acct_num,'[^[:digit:]]'),0)
where t2.created_date = date'2017-05-17'

ANSI-92

标准用于Join，ISO-8601标准用于date语法。

Answer 2

如果您使用的是Oracle 12.2 或更高版本，则可以将TO_NUMBER与ON CONVERSION ERROR子句一起使用，如下所示：

SELECT
H.COLUMN
FROM
TABLE1 H
JOIN TABLE2 C 
ON NVL(C.ACCT_NMBR, 0) = NVL(TO_NUMBER(H.V_ACCT_NUM DEFAULT -999 ON CONVERSION ERROR), 0)
-- use any number which is impossible in your column 
-- so that it do not match any random records.
WHERE
C.CREATED_DATE = DATE '2017-05-17';

注意：

• 使用标准ANSI联接
在
• 要将字符串转换为日期或TO_DATE函数的任何位置使用DATE文本

干杯！！

Answer 3

确定哪些数据是非数字的：

select * from table1 h
where regexp_like(h.v_acct_num, '[^0-9]')

并删除它们

.

希望对您有所帮助。