无效号码错误！似乎无法绕过它

Oracle 10g 数据库。我有一张桌子叫s_contact。这个表有一个字段叫做person_uid. This person_uid字段是 varchar2，但包含某些行的有效数字和其他行的无效数字。例如，一行可能有一个person_uid“2-lkjsdf”，另一个可能是 1234567890。

我只想返回 person_uid 中具有有效数字的行。我正在尝试的 SQL 是...

select person_uid 
from s_contact 
where decode(trim(translate(person_uid, '1234567890', ' ')), null, 'n', 'c') = 'n'

翻译会将所有数字替换为空格，以便如果字段仅包含数字，则修剪将导致 null。然后我使用解码语句设置一些要过滤的代码。 n=数字，c=字符。

当我只运行预览时，这似乎有效，但是当我添加过滤器时，我收到“无效数字”错误...

and person_uid = 100
-- or
and to_number(person_uid) = 100

我只是不明白发生了什么事！它应该过滤掉所有无效数字的记录，而 100 显然是一个数字......

有人有什么想法吗？不胜感激！

不幸的是，所提出的各种子查询方法并不能保证有效。 Oracle 可以将谓词推入子查询中，然后按照它认为合适的顺序评估条件。如果碰巧评估PERSON_UID在过滤掉非数字行之前，您会收到错误消息。乔纳森·根尼克（Jonathan Gennick）有一篇很棒的文章子查询疯狂 http://www.gennick.com/madness.html相当详细地讨论了这个问题。

这给你留下了一些选择

1）修改数据模型。通常，将数字存储在 NUMBER 列以外的任何内容中都不是一个好主意。除了导致此类问题之外，它还可能会破坏优化器的基数估计，从而导致查询计划不理想。

2) 更改条件以指定字符串值而不是数字。如果PERSON_UID应该是一个字符串，你的过滤条件可能是PERSON_UID = '100'。这避免了执行隐式转换的需要。

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;

进而my_to_number(PERSION_UID) = 100

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 无法推送vp.person_uid = 100在这里将谓词插入到子查询中，因为这样做会改变结果。您还可以使用提示来强制具体化子查询或防止谓词推送。