1

i am trying to execute a query but it keeps giving the error

ORA-01722: invalid number 01722. 00000 - "invalid number"

i checked if there is number like 01722. 00000 or similar in my table but there is not. I also have checked the query if i am using arithmetic operators in non numeric values, if i am comparing a numeric and a non numeric value together but still nothing. All of my table columns are varchar2 type It has been a full day now and i have not found how to fix it. The query is as below:

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;
MT0
  • 143,790
  • 11
  • 59
  • 117
Lira
  • 25
  • 1
  • 8

2 Answers2

0

I checked if there is number like 01722. 00000 or similar in my table

There's probably none. 01722 is Oracle error code:

ORA-01722 invalid number

Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

You posted a whole lot of code; I can't tell what might be causing it. Check code from CASE onwards, whether TO_CHAR function calls work correctly, whether you correctly used single quotes (or not), etc.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

All of my table columns are varchar2 type

That is likely to be part of your issue; you should be using the proper data types for all of your columns.

Given that is your current data model though, you're doing a few implicit conversions.

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

There are a number of problems here. The REPLACE() allears to be pointless as your format models don't use commas. The TRIM() calls could be avoided by adding the FM modifier to the format model. If AMOUNT is a varchar2 then comparing to a string with AMOUNT < '1' sort of makes sense, but leading zeros in that string value would cause issues, and it would be better to compare as a number rather than as a string (but then it should be a number anyway!). You could probably replace that whole expression with something like:

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

But the main problem is that to_char(AMOUNT,'0.99') or to any format model is implicitly converting the AMOUNT string to a number - it's really doing to_char(to_number(AMOUNT),'0.99') - so it's probably that (or the same thing later for ACCRUAL) which is erroring, because the table column contains string values which cannot be implicitly converted.

If you did select to_number(amount) from F_2019 you'd probably see the same error. Then you just have to figure out why - whether you have malformed data in that column, or if it is as you expect but the the implicit conversion isn't assuming the actual format.

Without seeing your data we can't tell which is the case, or how you should fix it. You either need to identify and fix malformed data, or exclude data that you know is right but shouldn't be convertable, or change the implicit conversions to explicit conversions with a format model that is correct for all of your data (as you did earlier with DATE_OF_BIRTH), possibly something like:

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

but obviously I'm completely guessing what your strings might look like. Your string might have comma as the decimal separator (guessing from the REPLACE()) so you might need/want to use the D marker instead, and specify the NLS settings as part of the conversion, as here.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318