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):
我有以下SQL查询,它在表中查找重复值(两个值为null必须被归类为重复,因此使用nvl):
select * from PersonLinkTable personLink
where personLink.RefPerson = 100
and nvl(to_char(personLink.PersonLinkType), '###') = nvl(to_char(PersonLinkTable.PersonLinkType), '###') // Repeats for required columns
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时,这被测试并导致“无效数字”Oracle错误。我相信如果将来添加任何日期列,则需要使用to_char。
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.
为什么以这种方式使用to_char会导致“无效数字”错误,以及使用它的正确方法是什么?目前的列类型是varchar2,char和number。
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.
此查询在SQL Server上正常工作,但有一些更改 - 没有to_char,isnull而不是nvl,null值更改为空字符串而不是'###'。这是一个更大的查询的一部分,并且围绕它“并且不存在”。
1 个解决方案
#1
1
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.
To_Char希望默认接收一个数字作为其参数。如果它收到一个字符串,那么它将尝试将字符串隐含地转换为数字,如果无法转换该字符串,则会引发此错误消息。
If the column is of a character type, do not try to convert it to a string.
如果列是字符类型,请不要尝试将其转换为字符串。
#1
1
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.
To_Char希望默认接收一个数字作为其参数。如果它收到一个字符串,那么它将尝试将字符串隐含地转换为数字,如果无法转换该字符串,则会引发此错误消息。
If the column is of a character type, do not try to convert it to a string.
如果列是字符类型,请不要尝试将其转换为字符串。