NUMBER data types

tracy · Post by **tracy** » Thu Jan 25, 2007 12:28 pm

We generally use DataStage to move data from sources to Oracle.

In Oracle, it's my understanding that there are different NUMBER data types:
NUMBER(6) would allow six digits max. I think that may mean 6 total including decimals, so you could have 4 before the decimal and 2 after, but not more than 6 total.
NUMBER(11,2) would allow 11, but 2 are reserved for the decimal.
NUMBER is unlimited in any direction.

So I figured we should just go with NUMBER since it will accomdate anything.

However, I'm not sure what the best way to use these data types in DataStage is.

Do you recommend using Decimal? What about the length and scale values?

I_Server_Whale · Post by **I_Server_Whale** » Thu Jan 25, 2007 12:38 pm

You could use 'Numeric'. Or the best would be to allow DataStage itself to figure out what the datatype would by importing the table definition from Oracle and use this metadata in your jobs.

HTH,
Whale.

DSguru2B · Post by **DSguru2B** » Thu Jan 25, 2007 1:15 pm

Use Decimal or Float depending upon the x and y values in NUMERIC(x,y). For each column it will be different and possibly same. But follow that. If you define it as integer, it will complain on the decimal point.

chulett · Post by **chulett** » Thu Jan 25, 2007 2:21 pm

Sometimes it just doesn't matter. I asked a similar question a couple of years ago.

tracy wrote:NUMBER(6) would allow six digits max. I think that may mean 6 total including decimals, so you could have 4 before the decimal and 2 after, but not more than 6 total.

No, not really. NUMBER(6) is syntactically identical to NUMBER(6,0) if that helps.

DSguru2B · Post by **DSguru2B** » Thu Jan 25, 2007 2:33 pm

I say stick to decimal. If you want the "after decimal digits" to be preserved after a mathamatical operation, you dont have to use FMT and ICONV/OCONV functions to maintain its format.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 25, 2007 2:45 pm

I say stick to import. That way the table definition is untouched by human hands and least likely to contain errors.

You are, of course permitted - nay, encouraged - to type in column descriptions where these are absent.

tracy · Post by **tracy** » Thu Jan 25, 2007 5:18 pm

Thanks for the tips. I didn't even think to use the import feature. I went ahead and did that and in case you are wondering, the NUMBER comes out to Decimal with a length of 38 and no scale. I'll stick with that.