调试Oracle中的数字溢出问题

发布于 2025-01-16 01:43:07 字数 282 浏览 0 评论 0原文

简而言之，在大约 820 万条记录中，我认为有一两条记录导致此语句出现问题：

to_number(regexp_substr(meas_value, '\d+(\.\d+)?', 1, 1)) num1

我已通过运行 Oracle 查询的 SAS 宏将数据集解析为 45 个较小的数据集。 44 个查询有效，1 个无效。

对于我的一生，我不知道如何“调试”Oracle 中的查询，以便我可以找到问题观察。我正在使用 Oracle SQL 开发人员。

先感谢您

原文

In short out of ~8.2 million records I think there is one or two records that is causing a hiccup with this statement:

to_number(regexp_substr(meas_value, '\d+(\.\d+)?', 1, 1)) num1

I have parsed out the dataset into 45 smaller ones via a SAS macro running the Oracle query.
44 queries work, 1 does not.

For the life of me I don't know how to "debug" the query in Oracle that I can find the problem observation. I am using Oracle SQL developer.

Thank you in advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

心房的律动 2025-01-23 01:43:07

您可以使用 validate_conversion 函数来过滤此类有问题的值：

将 (l, num) 作为 (
  选择
    等级，
    lpad('9', 等级*30, '9')
  从双
  按级别连接< 7
）
选择 *
从一个
其中 validate_conversion(num 作为数字) = 0
<前> L |编号
-: | :---------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------------
5 | 9999999999999999999999999999999999999999999999999999999999999999999999999999 9999999999999999999999999999999999999999999999999999999999999999999999999999
6 | 99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999

db<>fiddle 此处

You may use validate_conversion function to filter such problematic values:

with a (l, num) as (
  select
    level,
    lpad('9', level*30, '9')
  from dual
  connect by level < 7
)
select *
from a
where validate_conversion(num as number) = 0

 L | NUM                                                                                                                                                                                 
-: | :-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 5 | 999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999                              
 6 | 999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999

db<>fiddle here

回复收藏 0 原文

~没有更多了~