ORA-01722：无效号码01722.00000-"无效号码"

Question

我正在尝试执行查询，但它不断给出错误

ORA-01722：无效号码01722.00000-"无效号码"

我检查了是否有像01722这样的数字。00000或类似在我的表中，但没有。我还检查了查询，如果我在非数值中使用算术运算符，如果我正在比较一个数字和非数字值，但仍然什么都没有。我所有的表列都是varchar2类型，现在已经一整天了，我还没有找到如何解决它。查询如下：

select distinct 
 xmlroot(
 xmlelement("ftc:F_OE",
    xmlattributes('http://www.w3.org/2001/XMLSchema-instance' as 
      "xmlns:xsi", 
        'urn:oecd:ties:f:v1' as "xmlns",
        'urn:oecd:ties:f:v2' as "xmlns:ftc",
        'urn:oecd:ties:isoftypes:v1' as "xmlns:iso",
        'urn:oecd:ties:stftypes:v2' as "xmlns:sfa"      
    )
    ,          

    xmlelement("ftc:MessageSpec", 
        xmlelement("sfa:SendingCompanyIN", 'SL.008'),
        xmlelement("sfa:TransmittingCountry", 'AL'),
        xmlelement("sfa:ReceivingCountry", 'IT'),
        xmlelement("sfa:MessageType", 'F'),
        xmlelement("sfa:Warning", ''),
        xmlelement("sfa:Contact", ''),
        xmlelement("sfa:MessageRefId", 'SL.008.2018_aabb'),
        xmlelement("sfa:ReportingPeriod", '2018-12-31'),
        xmlelement("sfa:Timestamp", TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS'))                
    ,''),

    XMLAGG(xmlelement("ftc:F",

        xmlelement("ftc:ReportingFI",
            xmlelement("sfa:ResCountryCode", 'AL'),      
            xmlelement("sfa:TAX_ID", xmlattributes('IT' as "issuedBy"),'SL.008'),      
            xmlelement("sfa:Name", 'SHPK'),
            xmlelement("sfa:Address",
                xmlelement("sfa:CountryCode", 'AL'),
                xmlelement("sfa:AddressFix", 
                    xmlelement("sfa:Street", 'xxx'),
                    xmlelement("sfa:BuildingIdentifier", ''),
                    xmlelement("sfa:SuiteIdentifier", ''),
                    xmlelement("sfa:FloorIdentifier", ''),
                    xmlelement("sfa:DistrictName", ''),
                    xmlelement("sfa:POB", ''),
                    xmlelement("sfa:PostCode", '1234'),
                    xmlelement("sfa:City", 'YYYY'),
                    xmlelement("sfa:CountrySubentity", 'AB')
                ,''),-- sfa:AddressFix END
                xmlelement("sfa:AddressFree", 'streeetName, city, postalcode, AL')                                
            ,''), 
            xmlelement("ftc:FilerCategory", 'F604'),
            xmlelement("ftc:DocSpec",
                xmlelement("ftc:DocTypeIndic", 'F1'),
                xmlelement("ftc:DocRefId", CUSTOMER_NO),
                xmlelement("ftc:CorrMessageRefId", ''),
                xmlelement("ftc:CorrDocRefId", '')
            ,'')                   
        ,''),

        (
            SELECT xmlagg(
                xmlelement("ftc:ReportingGroup", 
                    xmlelement("ftc:AccountReport",
                        xmlelement("ftc:DocSpec",
                            xmlelement("ftc:DocTypeIndic", 'F1'),
                            xmlelement("ftc:DocRefId", CUSTOMER_NO),
                            xmlelement("ftc:CorrMessageRefId", ''),
                            xmlelement("ftc:CorrDocRefId", '')
                        ,'')
                        ,
                        xmlelement("ftc:AccountNumber", f2.ACC_NO),
                                xmlelement("ftc:AccountClosed", 'false'), 
                        xmlelement("ftc:AccountHolder", 
                            xmlelement("ftc:Individual",
                                xmlelement("sfa:ResCountryCode", 'AL'),
                                xmlelement("sfa:TAX_ID", xmlattributes('IT' as "issuedBy"),TAX_ID),
                                xmlelement("sfa:Name",
                                    xmlelement("sfa:PrecedingTitle",''),
                                    xmlelement("sfa:Title",''),
                                    xmlelement("sfa:FirstName",trim(first_name)),
                                    xmlelement("sfa:MiddleName",trim(MIDDLE_NAME)),
                                    xmlelement("sfa:NamePrefix",''),
                                    xmlelement("sfa:LastName",trim(last_name)),
                                    xmlelement("sfa:GenerationIdentifier",''),
                                    xmlelement("sfa:Suffix",''),
                                    xmlelement("sfa:GeneralSuffix",'')
                                ,'') 
                                ,
                                xmlelement("sfa:Address", 
                                    xmlelement("sfa:CountryCode", 'AL'),
                                    xmlelement("sfa:AddressFix", 
                                        xmlelement("sfa:Street", ''),   
                                        xmlelement("sfa:BuildingIdentifier", ''),
                                        xmlelement("sfa:SuiteIdentifier", ''),
                                        xmlelement("sfa:FloorIdentifier", ''),
                                        xmlelement("sfa:DistrictName", ''),
                                        xmlelement("sfa:POB", ''),
                                        xmlelement("sfa:PostCode", ''),
                                        xmlelement("sfa:City", ''),
                                        xmlelement("sfa:CountrySubentity", '')
                                    ,''),
                                    xmlelement("sfa:AddressFree", ADDRESS)                                
                                ,'')
                                ,
                                xmlelement("sfa:BirthInfo",
                                    xmlelement("sfa:BirthDate", to_char(TO_DATE(DATE_OF_BIRTH,'MM/DD/YYYY'),'YYYY-MM-DD')),
                                    xmlelement("sfa:City" , ''),
                                    xmlelement("sfa:CitySubentity", '')
                                ,'')                                                  
                            ,'') 
                        ,''), 

                        xmlelement("ftc:AccountBalance", xmlattributes(CCY as "currCode"),REPLACE(
          case 
            when AMOUNT < '1' then
            trim(to_char(AMOUNT,'0.99'))
            else
            trim(to_char(AMOUNT,'99999999999.99'))
            end,',','.') ),
          case when ACCRUAL <> '0' then
            xmlelement("ftc:Payment",
                xmlelement("ftc:Type",'FATCA502'),
                xmlelement("ftc:PaymentAmnt",xmlattributes(CCY as "currCode"),
                case 
            when ACCRUAL < '1' then
            trim(to_char(ACCRUAL,'0.99'))
            else
            trim(to_char(ACCRUAL,'99999999999.99'))
            end))
          end

                    ,'') 
                ,'')
            )
            FROM F_2019 f2
            WHERE f1.CUSTOMER_NO = f2.CUSTOMER_NO  
        ) 
    ,'')) 

,'') 
  ,VERSION '1.0') as F_2019
    from (select distinct CUSTOMER_NO, TAX_ID  from F_2019) f1;

Answer 1

我所有的表列都是varchar2类型

这可能是你问题的一部分；您应该为所有列使用正确的数据类型。

尽管这是您当前的数据模型，但是您正在进行一些隐式转换。

... REPLACE(
          case 
            when AMOUNT < '1' then
            trim(to_char(AMOUNT,'0.99'))
            else
            trim(to_char(AMOUNT,'99999999999.99'))
            end,',','.') ...

这里有许多问题。REPLACE（）可能毫无意义，因为您的格式模型不使用逗号。可以通过将FM修饰符添加到格式模型来避免TRIM（）调用。如果AMOUNT是varchar2，则与带有AMOUNT的字符串进行比较

... to_char(AMOUNT,'FM99999999990.00') ...

但主要问题是to_char（AMOUNT，'0.99'）或任何格式模型正在隐式地将AMOUNT字符串转换为数字-它实际上是在做to_char（to_number（AMOUNT），'0.99'）-所以它可能是错误的（或ACCRUAL后面的同样的事情），因为表列包含无法隐式转换的字符串值。

如果您确实从F_2019中选择了to_number（金额），您可能会看到相同的错误。然后您只需要找出原因——该列中是否有格式错误的数据，或者它是否如您所期望的那样，但隐式转换并未假设实际格式。

如果不查看您的数据，我们无法判断是哪种情况，或者您应该如何修复它。您要么需要识别和修复格式错误的数据，要么排除您知道正确但不应该可转换的数据，要么使用对所有数据都正确的格式模型将隐式转换更改为显式转换（就像您之前使用DATE_OF_BIRTH所做的那样），可能类似于：

... to_char(to_number(AMOUNT, '999,999,999,999.99'), 'FM99999999990.00') ...

但显然我完全猜测您的字符串可能是什么样子。您的字符串可能有逗号作为十进制分隔符（从REPLACE（）中猜测），因此您可能需要/想要使用D标记，并指定NLS设置作为转换的一部分，如下所示。

Answer 2

我检查了我的表中是否有01722.00000或类似的数字

可能没有。< code>01722是Oracle错误代码:

ORA-01722无效号码

原因:尝试将字符串转换为数字失败，因为该字符串不是有效的数字文本。算术函数或表达式中只能使用数字字段或包含数字数据的字符字段。只有数字字段可以添加到日期或从日期中减去。

你发布了大量的代码；我不知道是什么原因造成的。从＜code＞CASE＜/code＞开始检查代码，检查＜code＞TO_CHAR＜/code＞函数调用是否正常工作，是否正确使用了单引号等。