varchar to NUMBER conversion problem

[muascdev]

We have a column PROD_KEY which is defined as NUMBER in the table, its allowed to have NULL in it. when we receive data in a file, its received as VarChar, when we try to insert into table, it gives the following error, PendingSeqFile,0: Field "PROD_KEY" has import error and no default value; data: <empty>, at offset: 48, what do i need to do here, whats the best way to handle this. thx we use oracle9i

[ArndW]

Use the Designer to actually define a default null value for that column. In the input stage, highlight the column and right-mouse-click to "edit row" and set the null attributes.

[DSguru2B]

EDITED: After realizing its a px job.
Try this

Code: Select all

IF LEN(TRIM(in.PROD_KEY)) < 1 then SetNull() else in.PROD_KEY

[muascdev]

it worked with this if (isNull(col)) setnull() else StringToDecimal(col)