Oracle SQL Case 中的编号无效

Question

我遇到了一个SQL案例的麻烦，问题是我试图运行一个有7个不同列的案例，这些列可以有不同类型的数据(字符串，日期，数字)，取决于id。

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

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

该案例应该仅在列具有数字时才选择“then”。但是，当我运行它时，我得到一个无效的数字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

有什么建议可以帮助我完成这项工作吗。是一种变通方法还是一种不同的方法？

提前发送Thx。

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

我使用了下面发布的一些建议和解决方案，并使此代码samble工作。我将尝试在系统中实现它。Thx为帮助大家，你救了我很多头痛。

 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

Answer 1

不确定您为什么使用该Analytic函数。对于您的基本问题，这将起作用：

SELECT AVG(CASE
              WHEN ctrl_id = 1 THEN list_val1
              WHEN ctrl_id = 2 THEN list_val2
            END) AS avg_val
FROM sampledata3 qd

Answer 2

像AVG这样的聚合函数不能与VARCHAR数据类型一起使用，当使用此类函数时，NUMBER或INTEGER是必须的。

我修改了查询，用数字代替字符串，

WITH sampledata1
     AS (SELECT '1' ctrl_id, '23' list_val1, '43' list_val2 FROM DUAL),
     sampledata2
     AS (SELECT '2' ctrl_id, '34' 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

输出是

  AVG_VAL
----------
        23
        45

编辑1

也许您可以执行以下操作来首先确定行返回数字或非数字。

根据您的要求进行更改。

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),
     sampledata4
     AS (SELECT LENGTH (TRIM (TRANSLATE (ctrl_id, ' +-.0123456789', ' ')))
                   ctrl_id,
                LENGTH (TRIM (TRANSLATE (list_val1, ' +-.0123456789', ' ')))
                   list_val1,
                LENGTH (TRIM (TRANSLATE (list_val2, ' +-.0123456789', ' ')))
                   list_val2
           FROM sampledata3 qd                   --           group by ctrl_id
                              )
(  SELECT CASE WHEN ctrl_id IS NULL THEN AVG (ctrl_id) ELSE 0 END ctrl_id,
          CASE WHEN list_val1 IS NULL THEN AVG (list_val1) ELSE 0 END list_val1,
          CASE WHEN list_val2 IS NULL THEN AVG (list_val2) ELSE 0 END list_val2
     FROM sampledata4
 GROUP BY ctrl_id, list_val1, list_val2)

Answer 3

您可以尝试使用非数字符号过滤掉值，例如

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

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