oracle 无效号码错误 - [错误代码：1722，SQL 状态：42000] ORA-01722：无效号码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29394114/
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
Invalid number error - [Error Code: 1722, SQL State: 42000] ORA-01722: invalid number
提问by Ashish Verma
The 1st query from the below 2 queries is giving me [Error Code: 1722, SQL State: 42000] ORA-01722: invalid number error. But when I limit the no of records as in the 2nd query then it is running fine. Other than limiting the rows in the 2nd query, both the queries are identical.
以下 2 个查询中的第一个查询给了我 [错误代码：1722，SQL 状态：42000] ORA-01722：无效数字错误。但是当我在第二个查询中限制记录数时，它运行良好。除了限制第二个查询中的行之外，这两个查询是相同的。
SELECT b.first_name,
b.last_name,
b.device_derived,
b.ios_version_group,
b.add_date,
FIRST_VALUE (b.add_date)
OVER (PARTITION BY b.first_name, b.last_name, b.ios_version_group)
AS first_date,
LAST_VALUE (b.add_date)
OVER (PARTITION BY b.first_name, b.last_name, b.ios_version_group)
AS last_date
FROM (SELECT a.first_name,
a.last_name,
a.os_version,
a.device_type,
a.device,
a.add_date,
a.device_derived,
CASE
WHEN ( ( UPPER (a.device_derived) = 'IPHONE'
OR UPPER (a.device_derived) = 'IPAD')
AND TO_NUMBER (SUBSTR (a.os_version, 1, 1)) > 4)
THEN
'iOS ' || SUBSTR (a.os_version, 1, 1)
ELSE
'Others'
END
AS ios_version_group
FROM (SELECT first_name,
last_name,
os_version,
device_type,
device,
add_date,
CASE
WHEN UPPER (device_type) = 'ANDROID'
THEN
'Android'
WHEN UPPER (device_type) = 'BB'
OR UPPER (device_type) = 'BLACKBERRY'
THEN
'Blackberry'
WHEN UPPER (device_type) = 'IOS'
AND ( SUBSTR (UPPER (device), 1, 6) = 'IPHONE'
OR SUBSTR (UPPER (device), 1, 4) = 'IPOD')
THEN
'iPhone'
WHEN UPPER (device_type) = 'IOS'
AND (SUBSTR (UPPER (device), 1, 4) = 'IPAD')
THEN
'iPad'
END
AS device_derived
FROM vw_mobile_devices_all) a) b;
SELECT b.first_name,
b.last_name,
b.device_derived,
b.ios_version_group,
b.add_date,
FIRST_VALUE (b.add_date)
OVER (PARTITION BY b.first_name, b.last_name, b.ios_version_group)
AS first_date,
LAST_VALUE (b.add_date)
OVER (PARTITION BY b.first_name, b.last_name, b.ios_version_group)
AS last_date
FROM (SELECT a.first_name,
a.last_name,
a.os_version,
a.device_type,
a.device,
a.add_date,
a.device_derived,
CASE
WHEN ( ( UPPER (a.device_derived) = 'IPHONE'
OR UPPER (a.device_derived) = 'IPAD')
AND TO_NUMBER (SUBSTR (a.os_version, 1, 1)) > 4)
THEN
'iOS ' || SUBSTR (a.os_version, 1, 1)
ELSE
'Others'
END
AS ios_version_group
FROM (SELECT first_name,
last_name,
os_version,
device_type,
device,
add_date,
CASE
WHEN UPPER (device_type) = 'ANDROID'
THEN
'Android'
WHEN UPPER (device_type) = 'BB'
OR UPPER (device_type) = 'BLACKBERRY'
THEN
'Blackberry'
WHEN UPPER (device_type) = 'IOS'
AND ( SUBSTR (UPPER (device), 1, 6) = 'IPHONE'
OR SUBSTR (UPPER (device), 1, 4) = 'IPOD')
THEN
'iPhone'
WHEN UPPER (device_type) = 'IOS'
AND (SUBSTR (UPPER (device), 1, 4) = 'IPAD')
THEN
'iPad'
END
AS device_derived
FROM vw_mobile_devices_all) a) b
WHERE ROWNUM <= 100;
Can somebody tell me why I am getting this error. Is there an efficient way to write this query?
有人能告诉我为什么我会收到这个错误。有没有一种有效的方法来编写这个查询？
回答by Boneist
You have TO_NUMBER (SUBSTR (a.os_version, 1, 1)in your queries, so presumably you're hitting data that doesn't have a number at the start of the os_version, when you request more than 100 rows.
你有TO_NUMBER (SUBSTR (a.os_version, 1, 1)你的查询，所以当你请求超过 100 行时，你可能正在访问在 os_version 开始时没有数字的数据。
You need to check your data.
您需要检查您的数据。
回答by hol
This error happens when you try to convert a non-numeric value with TO_NUMBER.
当您尝试将非数字值转换为TO_NUMBER.
In the second query the first 100 rows seem not to result into a.os_versionto a non-numeric value.
在第二个查询中，前 100 行似乎不会导致a.os_version非数字值。
Try a simple select vw_mobile_devices_allto find the non-numeric os_version. Figure out how you can work around the problem. Maybe you can query the os_version differently.
尝试一个简单的选择vw_mobile_devices_all来查找非数字的 os_version。弄清楚如何解决这个问题。也许您可以以不同的方式查询 os_version。
