DSXchange

Hi

Im having trouble with decimal multiplication. I have a source field (Col1 datatype in oracle is Number) and one more field thats(col2 datatype in oracle is Number). here is a sample:

Col1=386.48
Col2=0.27225701061802341410291315001361285053 (no rounding is allowed)

Now if i do this calculation in oracle it works, but I couldnt do this in ds. I tried to convert this col2 into varchar2(42) by using to_char function (to_char(col2, '00D00000000000000000000000000000000000000')). but Inside the transformer i need to multiply col1 and col2, but here col1 is decimal and col2 is varchar, so it throwing warning and droping the records. (im limiting the rws to 2 rows from the source)

here is the warning message:

Handle_Null_Rejects,0: APT_Decimal::ErrorBase: From: APT_DecimalNumber::convertToDecimal: the precision is not sufficient..Record dropped.

Can somebody help me.

What precision and scale did you specify for the Decimal data type? Did you try increasing this when the "insufficient precision" message occurred?

Hi

Thanks for the reply.

I was using decimal(38,10) initially, but later on i changed it to decimal(38,38). But this didnt work either. SO i converted the result into varchar(42). Thats when i got this warning.

No I converted both the columns into varchar from the source. and i did the calculation.

Ex:

to_char(Col1)=386.48
to_char(Col2)=0.27225701061802341410291315001361285053

Now in the transformer im doing the following for the output column (outcol)


StringTodecimal(NullToZero(Col1) * NullToZero(Col2))

In DS im setting th target datatype to Decimal(11,3). This is fixing my requirement.

Now i have one doubt in this. The output from the derivation is comming as (say... 1223.514 exactly 3 digitas after the decimal point), is it because of the metadata that setting or any other reason.

Any advice would be helpful.

It's because of the scale value of 3.

Thanks Ray. Will it be same scale when I store it in table.

If the table has a scale definition of 3, yes.