Oracle“无效号码"由to_char函数引起 [英] Oracle "Invalid Number" caused by to_char function

问题描述

我有以下SQL查询，该查询正在表中查找重复的值(两个为null的值必须归类为重复的值，因此使用nvl):

I have the following SQL query which is looking for duplicate values in a table (two values being null must be classed as a duplicate, hence the use of nvl):

select * from PersonLinkTable personLink
where personLink.RefPerson = 100
and nvl(to_char(personLink.PersonLinkType), '###') = nvl(to_char(PersonLinkTable.PersonLinkType), '###')  // Repeats for required columns

第三行重复所需的列，并在以后添加任何新列时自动生成.问题是，当我添加to_char时，已对其进行了测试，并导致了"Invalid Number" Oracle错误.我相信to_char是必需的，以防将来添加任何日期列.

The third line repeats for the required columns, and is generated automatically in case any new columns are added in the future. The problem is that when I added to_char, this was tested and caused an "Invalid Number" Oracle error. I believe to_char is needed in case any date columns are added in the future.

为什么以这种方式使用to_char会导致无效数字"错误，正确的使用方式是什么?目前的列类型为varchar2，char和number.

Why would using to_char in this way cause an "Invalid Number" error, and what is the correct way to use it? The column types at the moment are varchar2, char and number.

此查询在SQL Server上进行了一些更改即可正常工作-不需要to_char，isnull而不是nvl，并且null值将更改为空字符串，而不是'###'.这是一个较大查询的一部分，并且周围存在不存在".

This query works correctly on SQL Server with some changes - no to_char, isnull instead of nvl and the null values are changed to empty strings instead of '###'. This is part of a larger query, and has "and not exists" around it.

推荐答案

To_Char希望默认情况下收到一个数字作为其参数.如果它接收到一个字符串，它将尝试将其隐式转换为数字，如果无法转换该字符串，则会出现此错误消息.

To_Char expects to receive a number as its argument by default. If it receives a character string then it will try to implictly convert the string to a number, and that will raise this error message if the string cannot be converted.

如果该列属于字符类型，请不要尝试将其转换为字符串.

If the column is of a character type, do not try to convert it to a string.

这篇关于Oracle“无效号码"由to_char函数引起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！