Arithmetic overflow error converting numeric to data type numeric error in sap BODS

[bittu_sharma]

Hi ,

We are getting the error Arithmetic overflow error converting numeric to data type numeric even though we have changed the data type length from 13,2 to 14,2.

please suggest.

Thanks

[jmuiruri]

Greetings @bittu_sharma,

What's the exact error message you are facing?

Best Regards,

Joseph

[bittu_sharma]

Hi Joseph,

here is the complete error-

                                                     SQL submitted to ODBC data source <SAPDSUAT> resulted in error <[Microsoft][ODBC Driver 17 for SQL Server][SQL
                                                     Server]Arithmetic overflow error converting numeric to data type numeric.
                                                     [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The statement has been terminated.>. The SQL submitted is <INSERT INTO
                                                     "DBO"."STG_USER_PROFILE_NSAP_MERGE_FULL" ( "SYSTEM" , "BUKRS" , "ROLEID" , "ROLEDESC" , "USERID" , "USER_NAME" , "PROLE" ,
                                                     "LTAMOUNT" , "LTCURR" , "FROMDATE" , "INVALID" , "TEAMOUNT" , "TECURR" , "DPROLEID" )  SELECT 'NSAP' , 
                                                     "STG_ZFI_HIERARCHY_NSAP_FULL"."BUKRS"  ,  "STG_ZFI_HIERARCHY_NSAP_FULL"."ROLEID"  ,  "STG_ZFI_HIERARCHY_NSAP_FULL"."ROLEDESC" 
                                                     ,  "STG_ZFI_HIERARCHY_NSAP_FULL"."USERID"  ,  "STG_USER_ADDR_NSAP_FULL"."NAME_TEXTC"  ,  "STG_ZFI_HIERARCHY_NSAP_FULL"."PROLE" 
                                                     ,  "STG_ZFI_HIERARCHY_NSAP_FULL"."LTAMOUNT"  ,  "STG_ZFI_HIERARCHY_NSAP_FULL"."LTCURR"  , 
                                                     "STG_ZFI_HIERARCHY_NSAP_FULL"."FROMDATE"  , (CASE 
                                                     WHEN ( "STG_ZFI_HIERARCHY_NSAP_FULL"."INACTIVE"  = 'X') THEN 'Y'
                                                     WHEN ( "STG_ZFI_HIERARCHY_NSAP_FULL"."INACTIVE"  = ' ') THEN 'N'
                                                     ELSE  "STG_ZFI_HIERARCHY_NSAP_FULL"."INACTIVE"  END)  ,  "STG_ZFI_HIERARCHY_NSAP_FULL"."TEAMOUNT"  , 
                                                     "STG_ZFI_HIERARCHY_NSAP_FULL"."TECURR"  ,  "STG_ZFI_HIERARCHY_NSAP_FULL"."DPROLEID"  FROM "DBO"."STG_USER_ADDR_NSAP_FULL"
                                                     "STG_USER_ADDR_NSAP_FULL" INNER JOIN "DBO"."STG_ZFI_HIERARCHY_NSAP_FULL" "STG_ZFI_HIERARCHY_NSAP_FULL" ON (
                                                     "STG_USER_ADDR_NSAP_FULL"."BNAME"  =  "STG_ZFI_HIERARCHY_NSAP_FULL"."USERID" ) >.

[jmuiruri]

Greetings bittu_sharma,

,On the target table under Options, set "Use Overflow File = Yes" and then re-run the Job and once the job completes open the overflow file and check the problematic column.Best Regards,Joseph

[bittu_sharma]

Thanks Joseph,

This worked.

Regards,

Bittu

[jmuiruri]

Hi bittu_sharma ,

Glad that worked, but i don't think it resolves the underlying issue. You need to investigate the column types for the target table on the database level.

For instance: The source could have a column of type numeric(17,1) and in the target the column type is numeric(17,0),

If try to load the data to the target you will face such issues, as the value from the source that you are trying to insert cannot fit into numeric(17,0) and so you will get an overflow error message.

The goal of the overflow was to capture the problematic records and you take a look at them to identify if they can fit into target column based on the data type defined in the target table as per the database.

Best Regards,

Joseph

[jmuiruri]

Hi bittu_sharma ,

Suppose you have two tables with the structure as shown below, the structure is slightly similar but the salary datatype is different.

## Let's create a source table
create table numeric_overflow_source(ss_id int identity(1,1),
f_name varchar(30)
, l_name varchar(30)
,start_date date
,end_date date
, salary numeric(17,2)
);

## Let's create a target table

create table numeric_overflow_target(ss_id int identity(1,1),
f_name varchar(30)
, l_name varchar(30)
,start_date date
,end_date date
, salary numeric(9,0)
)

During ETL you are probably doing 1:1 mapping so let's insert some data into the source table

insert into numeric_overflow_source values('John','Doe', '09/06/2023', '10/10/2023', 123456789012345.4)

What happens if we try to insert the same value to the target table?

insert into numeric_overflow_target values('John','Doe', '09/06/2023', '10/10/2023', 123456789012345.4)

The above insert will fail with an overflow error message as you can see below

The reason it fail's is because the value is too large to fit inside a numeric(9,0), bottom line, compare the data you are expecting to the table structure of the target, to ensure all edge cases are covered.

I the above example the numeric(9,0) will take not more than 9 characters to the left of the decimal and none from the right side of the decimal, in numeric(17,2) however, we will store a total of 17 characters with not more than 15 characters to the left of the decimal and 2 characters to the right side of the decimal.

Let's insert new records and let's observe how they are stored

insert into numeric_overflow_source values('John','Doe', '09/06/2023', '10/10/2023', 123456789012345.46786897)

insert into numeric_overflow_target values('John','Doe', '09/06/2023', '10/10/2023', 123456789.423)

Best Regards,

Joseph