Oracle SQL案例中的数字无效 [英] Invalid number in Oracle SQL Case

问题描述

我遇到了SQL案例的麻烦，问题是我试图用7个不同的列运行案例，这些列根据ID可以具有不同种类的数据(字符串，日期，数字).

Hi im habing trouble with a SQL case, the problem is im trying to run a case with 7 different columns, the columns can have different kinds of data (string,date,number) depending on an id.

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

This means that under some id's the rows in a column will be string while under other ids the rows in a column will be number.

我意识到这不是结构化数据库的常规用法，但是此特定表用于特定目的，过去该方法被认为是有用的.

I realise this isn't a conventional use of astructured database, but this specific table serve a specific purpose where this approach was deemed usefull in the past.

该情况应该仅在该列确实具有数字时才选择"then".但是，当我运行它时，我得到一个无效的号码ORA-01722.因为其中之一的行将包含字符串og和日期.

The case is supposed to only select a "then" when the column does have a number. However when i run it i get a invalid number ORA-01722. because one of the, rows will hold a string og date.

我正确地意识到了这一点，因为oracle在执行之前对sql进行了评估，并且没有顺序执行，因此即使实际上不必根据给定ID在该列上进行计算，在这些列上也会产生错误.

I realise its properly because oracle asses the sql before executing, and doesnt execute sequential, therefore giving errors on these column even though it wouldn actually have to calculate on the column under a given ID.

我试图执行的代码如下，'then'之前的硬编码1和2将根据ctrl_id(唯一ID)而变化，这将是我们只看一眼的安全代码和一个list_val列/行，一个数字

The code im trying to execute is the following, The hardcoded 1 and 2 before 'then' will change depending on ctrl_id (the unique id) and it will be the one securing that we only look and a list_val column/row with a number

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

关于如何进行这项工作的任何建议.解决方法还是其他方法?

Any suggestions to how i can make this work. either a workaround or a different approach ?

提前谢谢.

---------下面的解决方案

--------- Solution below

我使用了下面发布的一些建议和解决方案，并使此代码有效.我将尝试在系统中实现它.感谢您为大家节省的很多麻烦.

I used some of the suggestions and solutions posted below and got this code samble working. I will try and implement it with the system. Thx for the help everyone you saved me alot of headache.

 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

推荐答案

The aggregate function like AVG does not work with VARCHAR data type, NUMBER or INTEGER is a must when such functions are being used.

我已修改查询以使用数字而不是字符串

I have modified the query to have number instead of a string,

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

也许您可以执行以下操作来首先确定行返回NUMERIC或NON-NUMERIC.

Perhaps you can do something like the below to first ascertain row return NUMERIC or NON-NUMERIC.

根据您的要求进行更改.

Change as per your requirements.

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)

这篇关于Oracle SQL案例中的数字无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！