DSXchange

Posted: **Mon Sep 02, 2013 2:50 am**

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.

Posted: **Mon Sep 02, 2013 4:05 am**

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

Posted: **Mon Sep 02, 2013 4:19 am**

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

Posted: **Mon Sep 02, 2013 4:29 am**

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

Posted: **Mon Sep 02, 2013 4:52 am**

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

Posted: **Mon Sep 02, 2013 6:35 am**

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

Posted: **Mon Sep 02, 2013 7:03 am**

Hi Kunal,

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

Posted: **Mon Sep 02, 2013 7:24 am**

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

Posted: **Mon Sep 02, 2013 9:30 am**

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.

Posted: **Mon Sep 02, 2013 9:48 am**

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

DSXchange

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

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