DSXchange

Hi,
I need to handle a null column value.

Input is sequential file file.
One of the input column has null value.
Output is ORacle table and all columns are NOT Null.
In the transformer, i write
If IsNull(InRec.CITY_ADR) Then " " Else InRec.CITY_ADR

When I run the job and look in Director log, it has warning message
Customer..TransCust: ORA-01400: cannot insert NULL into ("Cust_t"."CITY_ADR")

Is there any other check I need to perform to avoid this error?

Thanks
Ketfos

Is the field of varchar datatype?

Hi,

No, it is Char(45). and Not Null


Thanks
Ketfos

Can you try writing it into a seq file to check the value.

You said your input is a sequential file and that it has a column with a null value. How did you accomplish that? I don't see how a sequential file can have a null value. An empty string perhap, but not a null value.

Perhaps you should say:

If InRec.CITY_ADR = "" Then " " else InRec.CITY_ADR

Try it and see.

Larry

Hi,
I wrote to seq file.
The column is empty.

Ketfos

Try writing some character and not space, see if this works.

Hi,


1. I changed the transformer condition to

If InRec.CITY_ADR = "" Then " " else InRec.CITY_ADR

It still gave the same error message - Cannot insert null value.


2. I was able to write a char into output file as requested by amsh76.

Let me know if get some other ideas or thought.
Thks

Ketfos

If I am not wrong, in Oracle, it does equivalent of trim on the column and thats the reason you are getting this problem.

Hi,

Amsh76 - What do I need to do to overcome this error?

Ketfos

What I can suggest is use, a different character and not blank for replacing null.

amsh76 wrote:If I am not wrong, in Oracle, it does equivalent of trim on the column and thats the reason you are getting this problem.

Sorry but you are wrong, Oracle stores spaces as spaces only. if column is varchar and its not null, it will accept spaces as its values.

Then why this error?