How to change decimal default value [38,10] to higher value
Moderators: chulett, rschirm, roy
How to change decimal default value [38,10] to higher value
I am getting the error where my column value is exceeding the default value [38,10].
job error is :
Fatal Error: APT_Decimal::assignFromString: the source string (322665901919233341505776723281119128249 ) is out of range for the decimal with precision 38.
I am able to read the data in source only after converting it Char by using TO_Char function.
Then i using Decimal_from_String function in modify stage.
I tried by putting transformer stage also but couldn't able to resolve either by converting it as "AsInteger" and giving my target meta data as Bigint for that column.
My job design is:-
Oracle Enterprise-------------->Modify--------->Oracle Enterprise
how to change the defualt Decimal[38,10]........when i am changing the environmental variable APT_DECIMAL_INTERM_PRECISION to [42] and APT_DECIMAL_INTERM_SCALE to [2] but most probably its changing the value during the runtime, not very sure about this.
any suggestion will be of gr8 help. Thanks in advance.
job error is :
Fatal Error: APT_Decimal::assignFromString: the source string (322665901919233341505776723281119128249 ) is out of range for the decimal with precision 38.
I am able to read the data in source only after converting it Char by using TO_Char function.
Then i using Decimal_from_String function in modify stage.
I tried by putting transformer stage also but couldn't able to resolve either by converting it as "AsInteger" and giving my target meta data as Bigint for that column.
My job design is:-
Oracle Enterprise-------------->Modify--------->Oracle Enterprise
how to change the defualt Decimal[38,10]........when i am changing the environmental variable APT_DECIMAL_INTERM_PRECISION to [42] and APT_DECIMAL_INTERM_SCALE to [2] but most probably its changing the value during the runtime, not very sure about this.
any suggestion will be of gr8 help. Thanks in advance.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Stop trying to put a 39-digit number into a field with preci
then how to handle this situation Ray?
I tried with both write method as load and upsert.
Please show some way to resolve this one.
I tried with both write method as load and upsert.
Please show some way to resolve this one.
[Why not try manually changing the metadata]
Hi Chulett,
Even if am changing it manually to numeric or bigint, i am being able to view the data.
The only way i am able to view is by converting that to Char by using TO_CHAR function in user defined sql query itself.
The data type is number at database level.
When i am runnig the by converting it again in transformer to Decimal the job is getting aborted after processing 80000 records.
I tried to capture the record in rejected file but nothing get populated there.
Please guide me resolve this one.
thanks in advance.[/quote]
Hi Chulett,
Even if am changing it manually to numeric or bigint, i am being able to view the data.
The only way i am able to view is by converting that to Char by using TO_CHAR function in user defined sql query itself.
The data type is number at database level.
When i am runnig the by converting it again in transformer to Decimal the job is getting aborted after processing 80000 records.
I tried to capture the record in rejected file but nothing get populated there.
Please guide me resolve this one.
thanks in advance.[/quote]
So try changing the metadata from Decimal(38,10) to Decimal(39) or Decimal(40). This is just a large Integer value with no fractional elements, yes?
Or at worst case use TO_CHAR in your source and then TO_NUMBER on your target side... shouldn't be needed, however.
Or at worst case use TO_CHAR in your source and then TO_NUMBER on your target side... shouldn't be needed, however.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It's not a DataStage issue.
Your number, with thousands delimiters, is 322,665,901,919,233,341,505,776,723,281,119,128,249
This makes it clear that it has 39 digits.
You can simply NOT store this as NUMBER(38), which can accommodate a maximum of 38 digits.
You MUST use a larger data type.
Your number, with thousands delimiters, is 322,665,901,919,233,341,505,776,723,281,119,128,249
This makes it clear that it has 39 digits.
You can simply NOT store this as NUMBER(38), which can accommodate a maximum of 38 digits.
You MUST use a larger data type.
Last edited by ray.wurlod on Sun Mar 11, 2007 12:57 am, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I'm assuming this means at both ends - source and target. And if it came out it sure as heck can go back in.san_deep wrote:The data type is number at database level.
Ray - NUMBER is not the same as NUMBER(38). I can put a 39 or 40 digit integer into a NUMBER field without loss of precision. I can put larger numbers into it but then start losing precision.
So, when I suggested changing the metadata - I meant at both ends, not just the target. Is that what you tried and it still isn't working for you, san_deep?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Thanks a lot Ray and Chulett for your time and info.
Please give me the other way round to fix this as i cann't respond a problem for a problem.
Thanks a lot big guns for your info and time.
I tried out the following ways:Chulett wrote: So, when I suggested changing the metadata - I meant at both ends, not just the target. Is that what you tried and it still isn't working for you, san_deep?
- [1.] I tried by changing the data type for that column at source with Decimal[42,2] and couldn't able to view the data itself.
[2.] I changed the datatype to numeric, and then bigint but got the same result at source.
[3.] The only way through which i was able to view data at source by converting it to CHAR by TO_CHAR function and changing the data type to varchar.
[4.] After this i tried first with stringToDecimal funtion in transformer and changed my TARGET DATA TYPE to Decimal[42,2]. But the job get aborted.
[5.] Then i tried it by using AsInteger function and changing TARGET DATA TYPE to big int,but got the same result.
Hi Ray can you please guide me what exactly to tell my Superior for this.Ray wrote:It's not a DataStage issue.
Please give me the other way round to fix this as i cann't respond a problem for a problem.
Can we change this default Decimal[38,10] to higher value.Chulett wrote: Hence the suggestion to 'fix' it.
How to send it back.Chulett wrote:I'm assuming this means at both ends - source and target. And if it came out it sure as heck can go back in.
Thanks a lot big guns for your info and time.
You can't change the default but you sure as heck can change the values after it imports them.
What happens when you make the metadata for that field Decimal(40) all the way through the job? No to_char, no StringToAnything, just pass it as a large decimal value with no scale from one end to the other?
If you continue to get errors, please be specific what they are. Saying it 'aborted' or you 'got the same result' doesn't really tell us anything. Post them.
What happens when you make the metadata for that field Decimal(40) all the way through the job? No to_char, no StringToAnything, just pass it as a large decimal value with no scale from one end to the other?
If you continue to get errors, please be specific what they are. Saying it 'aborted' or you 'got the same result' doesn't really tell us anything. Post them.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks Chulett.
Don't have access to DS right now i will check it and let you know as soon as possible.Chulett wrote:
1.you make the metadata for that field Decimal(40) all the way through the job.
2.If you continue to get errors, please be specific what they are. Saying it 'aborted' or you 'got the same result' doesn't really tell us anything. Post them.