topic Re: Cannot insert the value NULL into column in Talend Studio

Cannot insert the value NULL into column

Anonymous — Wed, 23 May 2018 11:06:57 GMT

I am joining Excel data and DB table data using tMap with ID as key. With this, DB table has to pull couple of column's more with the help of Key to Output but i am getting below error. Please suggest.

Cannot insert the value NULL into column 'MRN', table 'XYZ'; column does not allow nulls. INSERT fails.

Actually there's no null value in DB table data, but its still showing cant insert null value in MRN

Re: Cannot insert the value NULL into column

manodwhb — Wed, 23 May 2018 11:12:02 GMT

@samisyed80,since you were getting empty value for MRN column in XYZ table from source. you need to do the some default values.

row1.col==null?"ABC":row1.col

Re: Cannot insert the value NULL into column

TRF — Wed, 23 May 2018 11:12:26 GMT

Seems the columns MRN of the table XYC is declared with the NOT NULL constraint so you MUST give a value for this field when you try to insert a new row. There is no choice.

Why do you have a null value if the input table has not?

Unable to answer while you don't share your job design with the tMap.

Re: Cannot insert the value NULL into column

Anonymous — Wed, 23 May 2018 11:38:10 GMT

MRN is coming from the demographics table given below. Also find the tMap properties. Unfortunately, the target table is having zero rows.

Re: Cannot insert the value NULL into column

manodwhb — Wed, 23 May 2018 11:40:54 GMT

@samisyed80, you do not have the matched records between excel file and demographics ,since you were getting as null.

Re: Cannot insert the value NULL into column

Anonymous — Wed, 23 May 2018 11:49:13 GMT

Manohar, Yes there is. Its ID a common field

Re: Cannot insert the value NULL into column

manodwhb — Wed, 23 May 2018 11:53:25 GMT

@samisyed80,then do you have null values for MNR in demographics ?

Re: Cannot insert the value NULL into column

Anonymous — Wed, 23 May 2018 11:54:54 GMT

No... there is no null value for MRN in Demographics.

I have seen some mismatch in field length. MRN in Demographics is having length as String(20) but the target table is having the length as nvarchar(50). Does this causing null values?

Re: Cannot insert the value NULL into column

manodwhb — Wed, 23 May 2018 11:56:27 GMT

@samisyed80,if you are sure about there is no null values in MNR means ,your joining data is not matching please check.

Re: Cannot insert the value NULL into column

Anonymous — Wed, 23 May 2018 11:58:45 GMT

No... there is no null value for MRN in Demographics.

I have seen some mismatch in field length. MRN in Demographics is having length as String(20) but the target table is having the length as nvarchar(50). Does this causing null values?

Re: Cannot insert the value NULL into column

manodwhb — Wed, 23 May 2018 12:00:21 GMT

@samisyed80,filed length is doesn't matter,when you have matched data in soruce and lookup table.

Re: Cannot insert the value NULL into column

manodwhb — Wed, 23 May 2018 12:10:35 GMT

@samisyed80,since your were data my not correct due to format,what kind of data it has in Id column in excel file?

Re: Cannot insert the value NULL into column

TRF — Wed, 23 May 2018 12:10:37 GMT

By default, tMap use left outer join so if there is no matching row for a main row, MRN field will be null.
Use an inner join instead.

Re: Cannot insert the value NULL into column

Anonymous — Wed, 23 May 2018 13:19:49 GMT

Sorry, i was pointing to wrong table. Issue has been resolved now. Thank you.