topic Passing 'NULL' value into Not Nullable colum in Talend Studio

Passing 'NULL' value into Not Nullable colum

Anonymous — Sat, 16 Nov 2024 10:26:13 GMT

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

Re: Passing 'NULL' value into Not Nullable colum

Anonymous — Tue, 23 Aug 2016 03:29:24 GMT

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

Re: Passing 'NULL' value into Not Nullable colum

Anonymous — Thu, 25 Aug 2016 19:31:58 GMT

thanks 🙂 I will try and will let you know

Re: Passing 'NULL' value into Not Nullable colum

Anonymous — Fri, 26 Aug 2016 22:21:02 GMT

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

Re: Passing 'NULL' value into Not Nullable colum

Anonymous — Sat, 27 Aug 2016 12:50:12 GMT

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

Re: Passing 'NULL' value into Not Nullable colum

Anonymous — Sat, 27 Aug 2016 20:17:18 GMT

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

Re: Passing 'NULL' value into Not Nullable colum

Anonymous — Sat, 27 Aug 2016 20:39:43 GMT

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

Re: Passing 'NULL' value into Not Nullable colum

Anonymous — Sun, 28 Aug 2016 03:47:04 GMT

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