Sum REGEXP_SUBSTR导致ORA-01722:无效数字错误

Question

我正在创建一个对账报告，我主要是想知道每个发送批次的单据价值。

批处理 ID 位于其自己的列中，发送的数据位于一列中，并以逗号分隔，请参阅下面随机生成的示例：

002;BATCH12345;999;TEST;123940;9999;14052020;1234;123456789;123456;;;;;;FI;;;;;;1234567;;EUR;SPOT;10000000;;100,00;100,00;;Voucher Text;;;;;;;;;;;;;15052020;E;E;E;E;
002;BATCH12345;999;TEST2;123940;9999;14052020;1234;123456789;123456;;;;;;FI;;;;;;1234567;;EUR;SPOT;10000000;;250,00;250,00;;Voucher Text;;;;;;;;;;;;;15052020;E;E;E;E;

每个批次至少有2行，最大可以是2000行。在此表中，我有两列：Batch ID和Data

我需要将所有文件金额相加，在这种情况下为100 200。

目前，我正在尝试通过此查询完成工作：

SELECT batch_id, SUM(to_number(REGEXP_SUBSTR(data, '[^;]+', 1, 16)))
FROM table
GROUP BY batch_id

但是以错误ORA-01722结束:无效数字

提前感谢，我真的很感激这个网站给我的帮助

Answer 1

在这里，您的数据是按如下方式生成的：

SQL> select REGEXP_SUBSTR('002;BATCH12345;999;TEST;123940;9999;14052020;1234;123456789;123456;;;;;;FI;;;;;;1234567;;EUR;SPOT;10000000;;100,00;100,00;;Voucher Text;;;;;;;;;;;;;15052020;E;E;E;E;', '[^;]+', 1, 16) from dual;

REGEXP
------
100,00

SQL> select REGEXP_SUBSTR('002;BATCH12345;999;TEST2;123940;9999;14052020;1234;123456789;123456;;;;;;FI;;;;;;1234567;;EUR;SPOT;10000000;;250,00;250,00;;Voucher Text;;;;;;;;;;;;;15052020;E;E;E;E;', '[^;]+', 1, 16) from dual;

REGEXP
------
250,00

SQL>

表示，被视为字符串中的十进制字符。

让我用< code>NLS数字字符= '检查一下。，' (< code > decimal _ character group _ separator )

SQL> alter session set NLS_NUMERIC_CHARACTERS = '.,';

Session altered.

SQL> WITH YOUR_DATA(STR) AS
  2  (
  3  SELECT '002;BATCH12345;999;TEST;123940;9999;14052020;1234;123456789;123456;;;;;;FI;;;;;;1234567;;EUR;SPOT;10000000;;100,00;100,00;;Voucher Text;;;;;;;;;;;;;15052020;E;E;E;E;' FROM DUAL
  4  UNION ALL
  5  SELECT '002;BATCH12345;999;TEST2;123940;9999;14052020;1234;123456789;123456;;;;;;FI;;;;;;1234567;;EUR;SPOT;10000000;;250,00;250,00;;Voucher Text;;;;;;;;;;;;;15052020;E;E;E;E;' FROM DUAL
  6  )
  7  select SUM(to_number(REGEXP_SUBSTR(STR, '[^;]+', 1, 16))) from YOUR_DATA;
select SUM(to_number(REGEXP_SUBSTR(STR, '[^;]+', 1, 16))) from YOUR_DATA
                     *
ERROR at line 7:
ORA-01722: invalid number


SQL>

哦！！我也犯了同样的错误。因此，您需要使用＜code＞作为＜code＞NLS_NUMERIC_CHARACTERS＜/code＞参数。

您可以在会话中设置它并获取正确的数据，如下所示：

SQL> alter session set NLS_NUMERIC_CHARACTERS = ',.';

Session altered.
SQL>
SQL> WITH YOUR_DATA(STR) AS
  2  (
  3  SELECT '002;BATCH12345;999;TEST;123940;9999;14052020;1234;123456789;123456;;;;;;FI;;;;;;1234567;;EUR;SPOT;10000000;;100,00;100,00;;Voucher Text;;;;;;;;;;;;;15052020;E;E;E;E;' FROM DUAL
  4  UNION ALL
  5  SELECT '002;BATCH12345;999;TEST2;123940;9999;14052020;1234;123456789;123456;;;;;;FI;;;;;;1234567;;EUR;SPOT;10000000;;250,00;250,00;;Voucher Text;;;;;;;;;;;;;15052020;E;E;E;E;' FROM DUAL
  6  )
  7  select SUM(to_number(REGEXP_SUBSTR(STR, '[^;]+', 1, 16))) from YOUR_DATA;

SUM(TO_NUMBER(REGEXP_SUBSTR(STR,'[^;]+',1,16)))
-----------------------------------------------
                                            350

SQL>

您可以从oracle文档中了解有关＜code＞NLS_NUMERIC_CHARACTERS＜/code＞的更多信息。

要检查此< code>NLS参数的当前设置，可以使用以下查询:

SELECT *
  FROM NLS_SESSION_PARAMETERS
 WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';