Transfering data from VARCHAR field to a NUMBER field

yaminids · Post by **yaminids** » Mon Aug 07, 2006 4:51 pm

Hello friends,

I am getting the following error when I tried to transfer data from a VARCHAR field to NUMBER field
"ORA-01722: invalid number"

Is it possibe to convert the data (similar to to_number() function)
using a particular function in DataStage?

Any help would be highly appreciated
Yamini

kris007 · Post by **kris007** » Mon Aug 07, 2006 5:34 pm

I guess you can use NumericIsSigned transform.

yaminids · Post by **yaminids** » Mon Aug 07, 2006 5:53 pm

Hi Kris,

I got the same error (ORA-01722: invalid number) even after using NumericIsSigned function
This is how I used the function in the Transformer
NumericIsSigned(inLink.EMPR_ID)

Did I use the function incorrectly?
Can someone please help me with this?

Thanks
Yamini

chulett · Post by **chulett** » Mon Aug 07, 2006 6:06 pm

You actually don't need to do anything special to 'convert' a Varchar to a Numeric field in Server, it all happens automatically. There is an assumption, however, that what data is in the Varchar field is appropriate for a numeric field. Your error indicates that it isn't, meaning there are characters there that Oracle doesn't consider appropriate for a NUMBER field. Silly things like letters, perhaps.

Have you looked to see what is actually in the problem field? That will tell you what you need to do to solve the problem. It may be as simple as using the DIGITS transform, but without knowing what is causing the issue that would just be a guess.