Conversion from decimal(25,2) to decimal(14,2)

[prasson_ibm]

Hi,

I have source as Oracle and Target is Oracle.In my source dataype is Number and target is Number(14,2)
My select query is :-

Code: Select all

SELECT 
Round(Chain_Intake_Cost,2) As Chain_Intake_Cost
'X' As Dummy
From Table

And i have defined Chain_Intake_Cost column as Decimal(25,2) in source metadata.

But when precision for column is coming more then 14 that particulat record is getting dropped in Transformer with warning message.

Is there any way i can convert Decimal(25,2) to decimal(14,2) in transformer stage.

[srinivas.nettalam]

Is there any specific reason for defining the soruce metadata as 25,2 instead of 14,2?

[prasson_ibm]

Hi Srini,
If i define Decimla(14,2) in source metadata i get below error:-

Code: Select all

ORA_READ_TABLE,0: Fatal Error: APT_Decimal::assignFromString: the source string (-20567940872210.497284.46) is out of range for the decimal with precision 14.

So i have to increase precision for this column and handle somewhere in transformer.

Other option i tried to cast in source sql but no luck getting below error:-

Code: Select all

ORA-01438: value larger than specified precision allows for this column

[ray.wurlod]

Use DecimalToDecimal() function in Transformer stage or decimal_from_decimal() function in Modify stage. In the latter case specifying NOWARN may also prove useful.

[prasson_ibm]

Hi Ray,

When i use decimalToDecimal(Incol,'floor') i get warning in target as

Code: Select all

Conversion error calling conversion routine decimal_from_decimal data may have been lost

and the value is becoming 0.00

[priyadarshikunal]

The actual value has 20 precision digits, how are you planning to fit that in to 14,2 (12 precision digits)?

[prasson_ibm]

Hi Kunal,

I have done temporary fix as rejecting these records to reject table which are not fitting to Target Decimal(14,2).

[chulett]

How is that a "temporary" fix? What else could you possibly do? You can't put ten gallons in a five gallon bin.

[prasson_ibm]

Hi Chullet,

My job design is like below:-

Ora Src-------TSFM------> OraTgt-------->Reject

and i have defined below reject condition in oracle reject:-

sql error-data truncation.

I made target column type as varchar,and hence value more then (14,2) for this column is going to reject.

[chulett]

OK... and? Are you still looking for something from us with regards to this issue?