Numeric value out of range

suryapkakani · Post by **suryapkakani** » Wed Oct 28, 2009 1:56 pm

Hi I have two fields that are coming from source clmnt_id (Numeric 16) and indv_id (Numeric 10). The source is Oracle DB. I am taking indv_id (Numeric 17,1) as the output based on the following condition below

If (IsNull(out_t6xfrmOPTNTEVNTFACT.indv_id) or out_t6xfrmOPTNTEVNTFACT.indv_id = 0) then ((out_t6xfrmOPTNTEVNTFACT.clmnt_id + 0.1) ) else out_t6xfrmOPTNTEVNTFACT.indv_id

I maximum value of clmnt_id i see from source is 9999999999999999( 16 nine's)

When I run the job, I have the following error below

I get the following error below........

SQLSTATE=22003, DBMS.CODE=0

[DataStage][SQL Client][ODBC][IBM(DataDirect OEM)][ODBC SQL Server Driver]Numeric value out of range

Can I know how should i solve this problem

suryapkakani · Post by **suryapkakani** » Wed Oct 28, 2009 2:01 pm

The values of indv_id, I find in the log is as follows 100000000000000000 (17 zeroes)

ray.wurlod · Post by **ray.wurlod** » Wed Oct 28, 2009 2:51 pm

Numeric(17,1) can only have 16 digits to the left of the decimal point.

suryapkakani · Post by **suryapkakani** » Wed Oct 28, 2009 3:07 pm

Hi Ray,

The maximum value I have is 9999999999999999( 16 digit), and the output value as per my condition should have been 9999999999999999.1 (16 nine's and a 1 preceded with a decimal). The output format is (Numeric 17,1 ). Everything seems appropriate, why should my output generate 10000000000000000 (1 with 17 zero's). Is there anything wrong with the code or any modifications required. My Source is Oracle and Target is SQL server.

chulett · Post by **chulett** » Wed Oct 28, 2009 3:23 pm

Check again, it seems to be telling you that your maximum value is in fact 100000000000000000, if that's what you found in your log.

suryapkakani · Post by **suryapkakani** » Thu Oct 29, 2009 10:51 am

chulett wrote:Check again, it seems to be telling you that your maximum value is in fact 100000000000000000, if that's what you found in your log. ...

Hi chulett,

The maximum value from the source is 9999999999999999( 16 digit), but the transformation for some reason tried to get this value on to the ouput 100000000000000000. So I am having the Numerical Overflow. I did some analysis and found that length that Numeric holds is 18. It was the issue. So when I was adding up 0.1 to the number, it somehow converts it into 100000000000000000 rather than 9999999999999999.1. As a workaround i converted the source to have 9999999999 ( 10 9's) instead of 16 9's and this worked good for me.