How to change decimal default value [38,10] to higher value

san_deep · Post by **san_deep** » Sat Mar 10, 2007 9:11 am

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.

ray.wurlod · Post by **ray.wurlod** » Sat Mar 10, 2007 9:13 am

Stop trying to put a 39-digit number into a field with precision 38.

san_deep · Post by **san_deep** » Sat Mar 10, 2007 9:16 am

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.

chulett · Post by **chulett** » Sat Mar 10, 2007 10:05 am

What are your source and target datatypes? NUMBER? NUMBER(38)? Why not try manually changing the metadata to whatever you feel is appropriate based on those types and your data?

san_deep · Post by **san_deep** » Sat Mar 10, 2007 10:16 am

[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]

chulett · Post by **chulett** » Sat Mar 10, 2007 10:34 am

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.

san_deep · Post by **san_deep** » Sat Mar 10, 2007 11:09 am

Hi Chulett,

thanks for ur time and info.

But i am trying all this waht you have mentioned.
Its not working for me.

Is there any other way by which it can be achieved.

thanks in advance.

chulett · Post by **chulett** » Sat Mar 10, 2007 11:50 am

You'll need to wait and see if anyone else can help. Or call Support and open a case.

ray.wurlod · Post by **ray.wurlod** » Sat Mar 10, 2007 1:42 pm

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.

chulett · Post by **chulett** » Sat Mar 10, 2007 4:19 pm

san_deep wrote:The data type is number at database level.

I'm assuming this means at both ends - source and target. And if it came out it sure as heck can go back in.

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?

ray.wurlod · Post by **ray.wurlod** » Sun Mar 11, 2007 12:58 am

Yeah, but the OP had DECIMAL[38,10], which would only allow 28 digits to the left of the decimal place!

chulett · Post by **chulett** » Sun Mar 11, 2007 7:25 am

Right, that seems to be the default metadata of choice for an unbounded Oracle NUMBER.

Hence the suggestion to 'fix' it.

san_deep · Post by **san_deep** » Sun Mar 11, 2007 8:37 am

Thanks a lot Ray and Chulett for your time and info.

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?

I tried out the following ways:

[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.

Both the target and source table has same structure and this column is of datatype "NUMBER".

Ray wrote:It's not a DataStage issue.

Hi Ray can you please guide me what exactly to tell my Superior for this.
Please give me the other way round to fix this as i cann't respond a problem for a problem.

Chulett wrote: Hence the suggestion to 'fix' it.

Can we change this default Decimal[38,10] to higher value.

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.

How to send it back.

Thanks a lot big guns for your info and time.

chulett · Post by **chulett** » Sun Mar 11, 2007 9:32 am

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.

san_deep · Post by **san_deep** » Sun Mar 11, 2007 12:43 pm

Thanks Chulett.

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.

Don't have access to DS right now i will check it and let you know as soon as possible.