sql - to_number() 的另一个问题。我就是不明白

Question

我有一个主表(以下称为 SURVEY)和一个详细表(以下称为 ANSWERS)。不出所料，ANSWERS 有 SURVEY 问题的答案。 ANSWERS 有一个名为 TEXT 的 VARCHAR2 列。有些 ANSWERS.TEXT 值是真正的文本，但有些实际上是数字。幸运的是，我总是知道哪些行包含文本，哪些包含数字作为文本。

就是这样。我无法改变这一点。

回到过去，当某些 ANSWERS 行被保存时，它们的 TEXT 值被精心挑选并放入 SURVEY 表中正确键入的列中。一个简单的单表选择将获取 SURVEY 和特殊值。

但是现在，随着新应用程序的添加，我们删除了特殊列。相反，我们现在必须获取适当的 ANSWERS 行的 TEXT 值。

我创建了一个模拟旧的平凡选择语句的查询。它工作得很好......主要是。

这是一个片段:

select survey.*, 
       j2.overall_score
  from survey,
       (select to_number(trim(ANSWER.text)) overall_score, 
               survey.id survey_id 
          from ANSWER, 
               [edited - more SQL that gets the 'score' row from ANSWERS]) j2      
 where
   survey.id=j2.survey_id
   and overall_score > 70    

您可能会注意到 j2。在实际查询中，有六个这样的列，j1 到 j6。当我运行查询时，它看起来就像旧查询。你不能说它真的是从主/细节组装而成的。那是一种解脱!

但是，我的问题是“overall_score > 70”短语会导致“1722 invalid number”错误。当我不包含该短语时，Oracle 就像蛤蜊一样高兴，因此所有输出都通过 j2 的 to_number() 函数并且看起来不错。但是如果我添加条件，我就失败了。

where 子句的“overall_score”部分是根据从网页输入的搜索条件动态添加的。

我需要一些 fu 来告诉 Oracle 我真的知道我在做什么，请这样做。如果有非数字数据，ok，让j2的to_number()失败。凉爽的。但否则，就去做吧。

有什么好话吗？我是承包商，时间快到了。这是一个新要求:-/

Answer 1

我们创建了一个特殊版本的 to_number，它在内部捕获“1722 无效数字”异常并返回 0 而不是。在 sql 中用这个新函数替换 to_number 为我们消除了这个问题。