Null Values

ketfos · Post by **ketfos** » Tue May 24, 2005 2:51 pm

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

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue May 24, 2005 3:10 pm

Is the field of varchar datatype?

ketfos · Post by **ketfos** » Tue May 24, 2005 3:15 pm

Hi,

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

Thanks
Ketfos

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue May 24, 2005 3:19 pm

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

lebos · Post by **lebos** » Tue May 24, 2005 3:37 pm

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

ketfos · Post by **ketfos** » Tue May 24, 2005 3:46 pm

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

Ketfos

amsh76 · Post by **amsh76** » Tue May 24, 2005 3:50 pm

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

ketfos · Post by **ketfos** » Tue May 24, 2005 4:06 pm

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

amsh76 · Post by **amsh76** » Tue May 24, 2005 4:33 pm

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

ketfos · Post by **ketfos** » Tue May 24, 2005 4:59 pm

Hi,

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

Ketfos

amsh76 · Post by **amsh76** » Wed May 25, 2005 10:31 am

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

talk2shaanc · Post by **talk2shaanc** » Wed May 25, 2005 11:04 am

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.

amsh76 · Post by **amsh76** » Wed May 25, 2005 11:15 am

Then why this error?