SQL SELECT 查询因有效数字的 ORA-01722（无效数字）而失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28618792/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT query failing with ORA-01722 (invalid number) for valid numbers
提问by Michael Carman
I have a table which holds configuration options. The values are stored as strings but can represent different types of data. The TYPEcolumn identifies the data type of the value. (One of STRING, INTEGER, FLOAT, or BOOLEAN).
我有一个包含配置选项的表。这些值存储为字符串，但可以表示不同类型的数据。该TYPE列标识值的数据类型。（字符串、整数、浮点数或布尔值之一）。
CREATE TABLE CONFIG (
NAME VARCHAR2(256) NOT NULL,
TYPE VARCHAR2(20) NOT NULL,
VALUE VARCHAR2(1024)
)
I'm trying to run a query that includes a numeric comparison, e.g.
我正在尝试运行一个包含数字比较的查询，例如
SELECT NAME, VALUE FROM CONFIG
WHERE TYPE = 'INTEGER' AND to_number(VALUE) > 100
but my query fails with "ORA-01722: invalid number". None of the values (for records with type INTEGER) contain invalid characters. Why am I getting an "invalid number" error and how can I fix my query?
但我的查询因“ORA-01722：无效号码”而失败。没有任何值（对于类型为 INTEGER 的记录）包含无效字符。为什么我会收到“无效号码”错误，我该如何解决我的查询？
回答by Michael Carman
SQL doesn't define the order in which predicates (the conditions of a WHERE clause) are evaluated. Whether or not a query like this "works" depends on the execution plan selected by the server. Changes to your query that affect the plan could cause this error to appear and disappear with no apparent reason.
SQL 没有定义判断谓词（WHERE 子句的条件）的顺序。像这样的查询是否“有效”取决于服务器选择的执行计划。影响计划的查询更改可能会导致此错误在没有明显原因的情况下出现和消失。
The best solution is to not store numeric data in string (VARCHAR) fields.
最好的解决方案是不在字符串 (VARCHAR) 字段中存储数字数据。
Assuming that isn't an option you need to protect the call to to_number. You can do that with a CASE clause.
假设这不是您需要保护对to_number. 您可以使用 CASE 子句来做到这一点。
SELECT NAME, VALUE FROM CONFIG
WHERE (
CASE TYPE WHEN 'INTEGER'
THEN to_number(VALUE)
ELSE NULL
END) > 100
回答by Prem
You are getting error because to store a number you used a string instead of using a number.To fix your query convert the string to number in the DECODEand then use THAT result in the predicate(Condition in WHERE clause).
您收到错误，因为要存储您使用的字符串而不是使用数字的数字。要修复您的查询，将字符串转换为数字DECODE，然后在谓词中使用 THAT 结果（WHERE 子句中的条件）。
SELECT NAME,
VALUE
FROM CONFIG
WHERE TYPE = 'INTEGER'
AND DECODE ( (REPLACE(TRANSLATE(TRIM(VALUE),'0123456789','00000000000'),'0' ,NULL)), NULL, to_number(trim(VALUE)) ) > 100;
For more details,refer the link where someone has asked similar question: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:11504677087008
有关更多详细信息，请参阅有人提出类似问题的链接：https: //asktom.oracle.com/pls/apex/f?p=100:11:0 ::::p11_question_id: 11504677087008
