oracle 无效号码错误！似乎无法绕过它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1848146/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Invalid Number Error! Can't seem to get around it
提问by Nate
Oracle 10g DB. I have a table called s_contact. This table has a field called person_uid. This person_uidfield is a varchar2 but contains valid numbers for some rows and in-valid numbers for other rows. For instance, one row might have a person_uidof '2-lkjsdf' and another might be 1234567890.
Oracle 10g 数据库。我有一张桌子叫s_contact. 该表有一个名为 的字段person_uid。此person_uid字段是 varchar2，但包含某些行的有效数字和其他行的无效数字。例如，一行可能person_uid是“2-lkjsdf”，而另一行可能是 1234567890。
I want to return just the rows with valid numbers in person_uid. The SQL I am trying is...
我只想返回 person_uid 中具有有效数字的行。我正在尝试的 SQL 是...
select person_uid
from s_contact
where decode(trim(translate(person_uid, '1234567890', ' ')), null, 'n', 'c') = 'n'
The translate replaces all numbers with spaces so that a trim will result in null if the field only contained numbers. Then I use a decode statement to set a little code to filter on. n=number, c=char.
翻译将所有数字替换为空格，因此如果字段仅包含数字，则修剪将导致空值。然后我使用一个 decode 语句来设置一些要过滤的代码。n=数字，c=字符。
This seems to work when I run just a preview, but I get an 'invalid number' error when I add a filter of...
当我只运行预览时，这似乎有效，但是当我添加...的过滤器时出现“无效数字”错误。
and person_uid = 100
-- or
and to_number(person_uid) = 100
I just don't understand what is happening! It should be filtering out all the records that are invalid numbers and 100 is obviously a number...
我只是不明白发生了什么！它应该过滤掉所有无效数字的记录，100显然是一个数字......
Any ideas anyone? Greatly Appreciated!
任何人的想法？不胜感激！
回答by Justin Cave
Unfortunately, the various subquery approaches that have been proposed are not guaranteed to work. Oracle is allowed to push the predicate into the subquery and then evaluate the conditions in whatever order it deems appropriate. If it happens to evaluate the PERSON_UIDcondition before filtering out the non-numeric rows, you'll get an error. Jonathan Gennick has an excellent article Subquery Madnessthat discusses this issue in quite a bit of detail.
不幸的是，已经提出的各种子查询方法不能保证有效。允许 Oracle 将谓词推送到子查询中，然后以它认为合适的任何顺序评估条件。如果碰巧PERSON_UID在过滤掉非数字行之前评估条件，您将收到错误消息。Jonathan Gennick 有一篇很棒的文章Subquery Madness，其中详细讨论了这个问题。
That leaves you with a few options
这让你有几个选择
1) Rework the data model. It's generally not a good idea to store numbers in anything other than a NUMBER column. In addition to causing this sort of issue, it has a tendency to screw up the optimizer's cardinality estimates which leads to less than ideal query plans.
1) 重新设计数据模型。将数字存储在 NUMBER 列以外的任何内容中通常不是一个好主意。除了导致此类问题之外，它还倾向于搞砸优化器的基数估计，从而导致不太理想的查询计划。
2) Change the condition to specify a string value rather than a number. If PERSON_UIDis supposed to be a string, your filter condition could be PERSON_UID = '100'. That avoids the need to perform the implicit conversion.
2) 更改条件以指定字符串值而不是数字。如果PERSON_UID应该是字符串，则您的过滤条件可能是PERSON_UID = '100'. 这避免了执行隐式转换的需要。
3) Write a custom function that does the string to number conversion and ignores any errors and use that in your code, i.e.
3) 编写一个自定义函数来执行字符串到数字的转换并忽略任何错误并在您的代码中使用它，即
CREATE OR REPLACE FUNCTION my_to_number( p_arg IN VARCHAR2 )
RETURN NUMBER
IS
BEGIN
RETURN to_number( p_arg );
EXCEPTION
WHEN others THEN
RETURN NULL;
END;
and then my_to_number(PERSION_UID) = 100
进而 my_to_number(PERSION_UID) = 100
4) Use a subquery that prevents the predicate from being pushed. This can be done in a few different ways. I personally prefer throwing a ROWNUM into the subquery, i.e. building on OMG Ponies' solution
4) 使用防止谓词被推送的子查询。这可以通过几种不同的方式来完成。我个人更喜欢将 ROWNUM 放入子查询中，即基于 OMG Ponies 的解决方案
WITH valid_persons AS (
SELECT TO_NUMBER(c.person_uid) 'person_uid',
ROWNUM rn
FROM S_CONTACT c
WHERE REGEXP_LIKE(c.personuid, '[[:digit:]]'))
SELECT *
FROM valid_persons vp
WHERE vp.person_uid = 100
Oracle can't push the vp.person_uid = 100predicate into the subquery here because doing so would change the results. You could also use hints to force the subquery to be materialized or to prevent predicate pushing.
Oracle 无法将vp.person_uid = 100谓词推送到此处的子查询中，因为这样做会改变结果。您还可以使用提示来强制实现子查询或阻止谓词推送。
回答by Gary Myers
Another alternative is to combine the predicates:
另一种选择是组合谓词：
where case when translate(person_uid, '1234567890', ' ')) is null
then to_number(person_uid) end = 100
回答by Tom H
When you add those numbers to the WHERE clause it's still doing those checks. You can't guarantee the ordering within the WHERE clause. So, it still tries to compare 100 to '2-lkjsdf'.
当您将这些数字添加到 WHERE 子句时，它仍在进行这些检查。您不能保证 WHERE 子句中的顺序。因此，它仍然尝试将 100 与“2-lkjsdf”进行比较。
Can you use '100'?
你能用“100”吗？
回答by Dan
Another option is to apply a subselect
另一种选择是应用子选择
SELECT * FROM (
select person_uid
from s_contact
where decode(trim(translate(person_uid, '1234567890', ' ')), null, 'n', 'c') = 'n'
)
WHERE TO_NUMBER(PERSON_UID) = 100
回答by redcayuga
Regular expressions to the rescue!
正则表达式来拯救！
where regexp_like (person_uid, '^[0-9]+$')
where regexp_like (person_uid, '^[0-9]+$')
回答by NotMe
Use the first part of your query to generate a temp table. Then query the temp table based on person_uid = 100 or whatever.
使用查询的第一部分生成临时表。然后根据 person_uid = 100 或其他什么查询临时表。
The problem is that oracle tries to convert each person_uid to an int as it gets to it due to the additional and statement in your where clause. This behavior may or may not show up in the preview depending on what records where picked.
问题在于，由于 where 子句中的附加 and 语句，oracle 尝试将每个 person_uid 转换为 int 。此行为可能会或可能不会出现在预览中，具体取决于选取的记录。
