ORA-01722:Oracle 11g 的 Fr 区域设置的编号无效

Question

我有一个名为My_View的视图，其中包含一个具有数字十进制数据的varchar列。 同时，选择计算平均值时出现错误

ORA-01722：Fr 区域设置的号码无效

这是我尝试过的预言机查询，但收到错误：

select (AVG(MY_COLUMN)) 
from  MY_TABLE;
select TO_NUMBER((AVG(MY_COLUMN)), '90.9999', 'NLS_NUMERIC_CHARACTERS='',.''') 
from  MY_TABLE
GROUP BY MY_COLUMN;

如何摆脱这个错误？

Answer 1

从 Oracle 12.2 开始，可以使用on conversion error子句to_number()在转换失败时返回默认值。这对于您的用例来说很方便：您可以在转换错误时返回null，avg()会很乐意忽略该聚合函数。

select avg(
to_number(
my_column default null on conversion error, 
'9999d9999', 
'nls_numeric_characters = ''.,'''
)
) my_avg
from my_table;

Answer 2

问题似乎出在不是"数字"的数据中(为什么将数字保留在VARCHAR2列中(？例如，它包含"123A56"。该值的AVG是什么？

一个简单的选项是使用REGEXP_LIKE并仅对"有效"值执行数字运算。例如：

SQL> with test (col) as
2    (select '1234.56' from dual union all  -- valid
3     select '131'     from dual union all  -- valid
4     select 'ABC'     from dual union all  -- invalid
5     select 'xy.23'   from dual union all  -- invalid
6     select '.3598'   from dual union all  -- invalid
7     select '12.34.56'from dual            -- invalid
8    )
9  select col,
10         to_number(col, '9999D9999', 'nls_numeric_characters = ''.,''') col_as_num
11  from test
12  where regexp_like(col, '^d+.?d+$');
COL      COL_AS_NUM
-------- ----------
1234.56     1234,56
131             131
SQL>

现在您可以AVG以下值：

SQL> with test (col) as
2    (select '1234.56' from dual union all  -- valid
3     select '131'     from dual union all  -- valid
4     select 'ABC'     from dual union all  -- invalid
5     select 'xy.23'   from dual union all  -- invalid
6     select '.3598'   from dual union all  -- invalid
7     select '12.34.56'from dual            -- invalid
8    )
9  select avg(to_number(col, '9999D9999', 'nls_numeric_characters = ''.,''')) result
10  from test
11  where regexp_like(col, '^d+.?d+$');
RESULT
----------
682,78
SQL>