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
Numeric value out of range
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 47
- Joined: Wed Aug 20, 2008 7:31 am
- Location: New jersey
-
- Participant
- Posts: 47
- Joined: Wed Aug 20, 2008 7:31 am
- Location: New jersey
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 47
- Joined: Wed Aug 20, 2008 7:31 am
- Location: New jersey
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.
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.
sunny
-
- Participant
- Posts: 47
- Joined: Wed Aug 20, 2008 7:31 am
- Location: New jersey
Hi chulett,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. ...
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.
sunny