Page 1 of 1
Strange issue while converting varchar to integer
Posted: Thu Aug 08, 2013 11:50 am
by prasson_ibm
Hi,
My source and target is Oracle and one column STYLECOLOR is in source is VARCHAR and same column in Target is Integer and data will be interger only.
When source data is long integer value like "99991013751235" then in target its converted as "2147483647".
I am not applying any conversion function.Can someone help me to fix this issue.
Posted: Thu Aug 08, 2013 1:19 pm
by asorrell
You exceeded the Integer's bounds. 2,147,483,647 is the biggest number it can handle. Use big integer (BigInt) which supports up to 9,223,372,036,854,775,807.
Posted: Thu Aug 08, 2013 2:01 pm
by prasson_ibm
Target Oracle column is defined as NUMBER(16) and i think this value will fit into this datatype.
What conversion function i need to apply in transformer to convert varchar into Number(16)
Posted: Thu Aug 08, 2013 3:35 pm
by jwiles
Is the target defined as Number(16) or Integer? You've stated both
It's not the conversion that's the problem, it's the datatype the value is being placed into. Make sure your datatype out of the transformer can hold the value. As Andy stated, an Integer cannot hold a value that large. Either a BigInt or a Decimal of appropriate size (16) would be acceptable datatypes.
Regards,
Posted: Wed Aug 14, 2013 6:55 am
by srinivas.nettalam
That is why I used to mark similar columns as BigInt in datastage when mapping to the ODWH.
Posted: Wed Aug 14, 2013 12:49 pm
by prasson_ibm
Yes but some of them are left unmarked and hence identified as issue.It resolved now....