Oracle SQL 案例中的数字无效

您好，我在处理 SQL 案例时遇到了麻烦，问题是我尝试运行具有 7 个不同列的案例，这些列可以具有不同类型的数据（字符串、日期、数字），具体取决于 id。

这意味着在某些 id 下，列中的行将是字符串，而在其他 id 下，列中的行将是数字。

我意识到这不是结构化数据库的常规用法，但是这个特定的表有一个特定的目的，这种方法在过去被认为是有用的。

当该列确实有数字时，这种情况应该只选择“然后”。但是，当我运行它时，我得到一个无效的数字 ORA-01722。因为其中一行将保存一个字符串 og 日期。

我正确地认识到了这一点，因为oracle在执行之前评估了sql，并且不按顺序执行，因此即使它实际上必须在给定ID下的列上进行计算，也会在这些列上给出错误。

我尝试执行的代码如下，“then”之前的硬编码 1 和 2 将根据 ctrl_id （唯一 id）而变化，它将是确保我们只查看的代码和带有数字的 list_val 列/行

WITH sampledata1 AS
 (SELECT '1' ctrl_id, '23' list_val1, 'Textfield' list_val2
    FROM dual),
sampledata2 AS
 (SELECT '2' ctrl_id, 'Textfield' list_val1, '45' list_val2
    FROM dual),
sampledata3 AS
 (SELECT *
    FROM sampledata1
  UNION
  SELECT *
    FROM sampledata2)
SELECT CASE
          WHEN ctrl_id = 1 THEN
           AVG(list_val1)
           over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC)
          WHEN ctrl_id = 2 THEN
           AVG(list_val2)
           over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC)
        END AS avg_val
  FROM sampledata3 qd

关于我如何完成这项工作的任何建议。要么是解决方法，要么是不同的方法？

提前谢谢。

--------- 解决方案如下

我使用了下面发布的一些建议和解决方案，并使该代码样本正常工作。我会尝试用系统来实现它。感谢大家的帮助，让我免去了很多头痛。

 WITH sampledata1
     AS (SELECT '1' ctrl_id, '23' list_val1, 'Textfield' list_val2 FROM DUAL),
     sampledata2
     AS (SELECT '2' ctrl_id, 'Textfield' list_val1, '45' list_val2 FROM DUAL),
     sampledata3
     AS (SELECT * FROM sampledata1
         UNION
         SELECT * FROM sampledata2)
 select ctrl_id,
 avg(CASE WHEN TRIM(TRANSLATE(list_val1, ' +-.0123456789', ' ')) is null 
 then list_val1 else null end) over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC) list_val1,
    avg(CASE WHEN TRIM(TRANSLATE(list_val2, ' +-.0123456789', ' ')) is null 
 then list_val2 else null end) over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC) list_val2
       
            from   sampledata3 qd

您可以尝试使用类似的方法过滤掉带有非数字符号的值

AVG(CASE WHEN TRIM(TRANSLATE(list_val1, ' +-.0123456789', ' ')) is null then list_val1 else null end) OVER (...)

注意！不幸的是，像“+12-.3”这样的字符串也会被识别为数字，在这种情况下，您将得到相同的 ora-01722