One Star

Passing 'NULL' value into Not Nullable colum

Hi Guys,
So here is the situation. I have column Firs_name ( not Null), last_name(not Null), but some of the data into the column has value is 'NULL' char. when I'm loading data from flat file its not allowing to load into the target table and giving me error saying column can not be null. So I'm assuming that when I'm loading data into target table, talend is reading 'NULL' value as 'NULL' record.
Here is the example ..
First_Name Last_Name
ABC          XYZ
EFG          HIJ
HIK           NULL    -----> this the exactly writing in source column, as rule if some one do not want to use their last name then, agent need to put NULL as last name.
Is there any way that I can pass this same exact data into target table.
Thanks
7 REPLIES
Community Manager

Re: Passing 'NULL' value into Not Nullable colum

Hi 
You are not allowed to insert a Null value into a Not nullable column. As a workaround, convert Null to empty value or set its a default value. For example:
tFileInputDelimited--main-t-tMap--main--tMysqlOutput
on tMap, you can transfer the Null value to empty value or set it to a default value.
row1.Last_Name==null?"":row1.Last_name
or 
row1.Last_Name==null?"default value":row1.Last_name
Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Passing 'NULL' value into Not Nullable colum

thanks :-) I will try and will let you know
One Star

Re: Passing 'NULL' value into Not Nullable colum

I tried this verius way but still not working
1. row3.last_name==null?"NULL":row1.last_name   ----> still getting null value error
2. row3.LST_NM.equals("NULL")?"":row3.LST_NM -----> this will replace "NULL" character in column with Blank value in target
3. row3.Last_name==?"":row3,last_name   ---> Still getting error
4. relational.isnull(row3.last_name)?"":row1.last_name ---> still getting error
this is the error
ERROR:  Column 4 : Field cannot contain null values
any other solution
Try to run attached test file. First_name and Last_name both column is Not Nullable. I'm looking same value each column as in my target table. including "NULL" written in the target column where it says "NULL" in file.
Please let me know
Testfill_NULL.txt.txt
Community Manager

Re: Passing 'NULL' value into Not Nullable colum

Which type of database? Mysql?
As I said in previous post, you are not allowed to insert a NUll value into a not nullable column, however, you can put an empty value or a default value, have you tried this?
row3.last_name==null?"":row1.last_name
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Passing 'NULL' value into Not Nullable colum

we are using flat file as input and Netezza for target.
Yes I tried Default value as "NULL" but it still give me error.
The default value will help if there is Empty("") record then it will add 'NULL' as default. but if column has 'NULL' as last name then it will still gives error
One Star

Re: Passing 'NULL' value into Not Nullable colum

I use row3.last_name==null?"":row1.last_name  and made that column default value 'NULL'  and last_name column is not nullable. We are using Netezza database.
ERROR: Field cannot contain null values
Community Manager

Re: Passing 'NULL' value into Not Nullable colum

Hi  
I think Netezza still consider empty "" value as null, you can either change the default value to another one such as "no data" or modify the column property to nullable in database. 
Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business