Cannot read large number value

radarada · Post by **radarada** » Sun Nov 30, 2008 10:56 am

DS is reading from an oracle table that contains a NUMBER data type. One of the records in this field is a large number of 30 7's. 777777777777777777777777777777 I cannot get DS to load this value into a Decimal 38,10. DS is unable to read it in using a Decimal (38,10). The only way for the value to come into DS is to TO_CHAR the field from Oracle. If I then try to StringToDecimal the value it will come out as 0. If I try to load the varchar number directly into a Decimal (38,10) then the value will be treated as a null record and dropped. Is this related to the bug in DS for version 7.5.2 on reading in large NUMBER values? Thanks

chulett · Post by **chulett** » Sun Nov 30, 2008 11:33 am

You can't fit a 30 digit integer into a "38,10" decimal field. You should be reading an unbounded NUMBER field as "38,0". What is your target?

radarada · Post by **radarada** » Sun Nov 30, 2008 11:37 am

An Oracle NUMBER corresponds to a DECIMAL(38,10) in DS. The target is also NUMBER.

Mike · Post by **Mike** » Sun Nov 30, 2008 1:00 pm

Not relevant. The fact is that a 30-digit integer is not going to fit in a decimal(38,10) field. You need at least a decimal(30,0). Change the metadata as Craig suggests. If your target is also a decimal(38,10), you'll need to change that as well.

Mike

radarada · Post by **radarada** » Sun Nov 30, 2008 2:14 pm

Not to dwell on this issue but an Oracle Number with no precision or scale defined can still allow decimals up to 10. By default a NUMBER is really NUMBER(38,10) in oracle. Defining it as 30,0 as suggested would not allow any decimals (if a number had decimals). I should be able to load any number into that field (less then 38) but for some reason DS has an issue with this large NUMBER. This field also contains numbers with only 1 or 2 digit numbers and all load fine. In fact out of 3.9million rows only this one row fell out. I appreciate the help but I am looking for responses related to the bug in 7.5.2 regarding how DS treats a NUMBER data type in DS that obtains a large value. Thanks

ray.wurlod · Post by **ray.wurlod** » Sun Nov 30, 2008 2:52 pm

Go bigger. Try Decimal(48,10).

Mike · Post by **Mike** » Sun Nov 30, 2008 2:58 pm

No. NUMBER in Oracle is not the same as NUMBER(38,10). NUMBER(38,10) is a fixed point number with precision 38 and scale 10. NUMBER is a floating point number.

When you import a column defined as NUMBER, DataStage somewhat arbitrarily sets the metadata to DECIMAL(38,10). That probably works in 99.9999% of the cases, but you are operating outside of that with your 30 digit integer.

I suggest changing the metadata to DECIMAL(38,8). That will handle your 30 digit integer and only sacrifice a couple of decimal places in scale for the remainder of the file.

You could also try switching to float instead of decimal.

Mike

chulett · Post by **chulett** » Sun Nov 30, 2008 3:27 pm

radarada wrote:Not to dwell on this issue but an Oracle Number with no precision or scale defined can still allow decimals up to 10. By default a NUMBER is really NUMBER(38,10) in oracle.

Not to dwell on this issue, but you're wrong. As Mike notes, your "38,10" comes from what DataStage decides during the import what an unbounded number should be, who knows why. A NUMBER without precision and scale is a floating point number. Precision values go up to 38. Scale values range from -84 to 127. My suggestion of "38,0" was based on the apparently erroneous assumption that you were using one to store integer values, based on your single example.

Only you know what your data value ranges are in that field. Adjust the metadata accordingly.