Equivalent Oracle TO_NUMBER function

[reddy]

Hi Guys,

Can you guys please clarify my doubt on Oracle to_number function equivalent in Datastage.

I have a inputfield varchar(10) and output oracle filed is Number.

In oracle we will give to_number(fieda).I know in Datastage we will move directly from varchar to numeric without any conversion.

Please clarify my doubt. on do we any conversion to move from character to numeric viceversa...

Thanks in advance
Reddy

[kumar_s]

DataStage will do this conversion Implicitly with compatiable values.

[kcbland]

Test and see. It's up to you to make sure the data is numeric if you're unsure to its quality. You'll want to verify the data type and precision prior to attempting to load it into the database. A library of data validation functions would sure be a good thing to have....hint hint...

A good series of functions could be things like:

1. MakeIntegerElseNULL
2. MakeIntegerElseZero
3. MakeISODateElseNULL
4. MakeDecimal0ElseZero (rounds numeric values to 0 scale)
5. MakeDecimal1ElseZero (rounds numeric values to 1 scale)
6. MakeDecimal2ElseZero (rounds numeric values to 2 scale)
7. MakeDecimal3ElseZero (rounds numeric values to 3 scale)
8. MakeDecimal4ElseZero (rounds numeric values to 4 scale)

Use the appropriate function after each target column derivation to conform the data to the appropriate data type, length, and scale.