在Oracle SQL Case中出现了无效的数字。
在Oracle SQL Case中出现了无效的数字。
大家好，我在处理一个SQL案例时遇到了问题。问题在于我想要运行一个包含7个不同列的案例，这些列可能包含不同类型的数据（字符串、日期、数字），具体取决于一个ID。\n这意味着在某些ID下，某列中的行将是字符串，而在其他ID下，某列中的行将是数字。\n我意识到这不是一个常规的结构化数据库使用方式，但这个特定的表在过去被认为是有用的。\n这个案例只有在列中包含数字时才选择“then”。然而，当我运行它时，我会得到一个无效的数字ORA-01722错误，因为其中一个行将包含字符串或日期。\n我意识到这可能是因为Oracle在执行之前评估了SQL，并且不按顺序执行，因此在这些列上给出错误，即使在给定ID下实际上不需要对该列进行计算。\n我尝试执行的代码如下所示，\'then\'之前的硬编码的1和2将根据ctrl_id（唯一ID）进行更改，并且它将确保我们只查找具有数字的list_val列/行\n
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
\n有没有关于如何让这个工作的建议？要么是变通方法，要么是不同的方法？\n提前感谢大家的帮助。\n---------以下是解决方案\n我使用了下面发布的一些建议和解决方案，并使得这个样本代码工作。我将尝试将其与系统一起实施。非常感谢大家的帮助，你们帮我解决了很多麻烦。\n
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
在Oracle SQL中，偶尔会遇到"Invalid number"错误。这个错误通常是由于数据类型不匹配导致的，即试图将一个非数字类型的值转换为数字类型时出错。
对于这个问题，可以使用以下方法解决：
SELECT AVG(CASE
WHEN ctrl_id = 1 THEN list_val1
WHEN ctrl_id = 2 THEN list_val2
END) AS avg_val
FROM sampledata3 qd
以上代码中使用了AVG函数，该函数用于计算指定列的平均值。在CASE语句中，当ctrl_id等于1时，返回list_val1的值；当ctrl_id等于2时，返回list_val2的值。最后，将这些值的平均值作为avg_val返回。
这种方法可以避免在转换非数字类型值时出现"Invalid number"错误。
在Oracle SQL中，当使用AVG等聚合函数时，不支持VARCHAR数据类型，必须使用NUMBER或INTEGER数据类型。
根据给定的查询语句，输出结果为：
AVG_VAL
----------
23
45
为了解决这个问题，可以通过以下方式确定返回的行是数值还是非数值：
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)
( 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)
这个版本几乎解决了问题，但它只显示了文本字段的长度（9），然而结合其他答案，解决了我的问题。非常感谢您的时间：）我会将解决方案添加到我的原始问题中。
非常高兴知道问题已解决。
在Oracle SQL中出现"Invalid number"错误的根本原因是在对非数字符号进行过滤时，使用了不正确的方法或表达式。这可能导致无效的数字值被识别为有效的数字，从而导致错误的发生。
为了解决这个问题，可以尝试使用以下方法来过滤掉带有非数字符号的值：
AVG(CASE WHEN TRIM(TRANSLATE(list_val1, ' +-.0123456789', ' ')) is null then list_val1 else null end) OVER (...)
需要注意的是，使用这种方法时，可能会将类似"+12-.3"这样的字符串错误地识别为有效的数字，从而导致相同的"ora-01722"错误。因此，在使用这种方法时需要谨慎。
通过将这个方法与其他答案结合使用，可以解决此问题。感谢提供答案的人，非常感谢您的帮助：）