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

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
prasson_ibm
Premium Member
Premium Member
Posts: 536
Joined: Thu Oct 11, 2007 1:48 am
Location: Bangalore

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

Post by 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
Participant
Posts: 134
Joined: Tue Jun 15, 2010 2:10 am
Location: Bangalore

Post by srinivas.nettalam »

Is there any specific reason for defining the soruce metadata as 25,2 instead of 14,2?
N.Srinivas
India.
prasson_ibm
Premium Member
Premium Member
Posts: 536
Joined: Thu Oct 11, 2007 1:48 am
Location: Bangalore

Post by 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
:cry: :cry:
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by 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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
prasson_ibm
Premium Member
Premium Member
Posts: 536
Joined: Thu Oct 11, 2007 1:48 am
Location: Bangalore

Post by 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
Premium Member
Premium Member
Posts: 1735
Joined: Thu Mar 01, 2007 5:44 am
Location: Troy, MI

Post by priyadarshikunal »

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

Genius may have its limitations, but stupidity is not thus handicapped. :wink:
prasson_ibm
Premium Member
Premium Member
Posts: 536
Joined: Thu Oct 11, 2007 1:48 am
Location: Bangalore

Post by 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
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
prasson_ibm
Premium Member
Premium Member
Posts: 536
Joined: Thu Oct 11, 2007 1:48 am
Location: Bangalore

Post by 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
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Post Reply