Oracle Numeric data type precision and scale

raoraghunandan · Post by **raoraghunandan** » Sat Mar 05, 2005 2:45 pm

Hi All,
A column defined in Oracle9i as NUMBER gets translated as Decimal with length as 38 and scale as 0 OCI9i Stage.

I suspect that this is causing rounding up of numeric values to some extent and could be the reason for some of the mismatches when we do tests like
select sum(col1) from table
in target and source

Have anyone faced similar problems before and if so any suggestions?

Thanks,
Rao

kcbland · Post by **kcbland** » Sat Mar 05, 2005 9:53 pm

Always visually inspect and correct your metadata once imported. Not having the correct scale will cause DataStage to round values. It's kind of spurious, it only re-scales the data if you manipulate the column in some fashion, such as adding columns. Sorry 'bout that.

ray.wurlod · Post by **ray.wurlod** » Sat Mar 05, 2005 10:26 pm

Beware that the DataStage data browser manifests the kind of behaviour that Ken described, for exactly the same reason; it's driven by the metadata definitions.
Always check with a different tool, such as sqlplus or TOAD, when you suspect that some rounding is happening; the DataStage "rounding" is often only for display purposes and it handles actual processing of the data correctly.