Handle null value in key columns

One Star

Handle null value in key columns

On tOracleOutput for update or delete action, we need to specify the key columns. In my case, some key columns could be null. I expected that for one column, null == null. Or we could specify NVL function for the columns as in sql. 
Is there such option in tOracleOutput on specifying key columns? Is there an easy way to match value as null == null for key columns?
Community Manager

Re: Handle null value in key columns

Hi 
For update, it is other columns are updated, not the key column. Can you please explain your requirement with some example data?
Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Handle null value in key columns

For example, I have a table with 3 columns, (key1, key2, value). column key1 is not nullable, while key2 is nullable. On update , the job will check on the key columns to see if they are the same. I hope, (source.key1 =1, source.key2=null), will match with (target.key1=1, target.key2=null), so the target.value is updated. But from my testing, target row is not updated. 
Is there anyway to make it happen?
One Star

Re: Handle null value in key columns

Anyone has any suggestion on this?
Six Stars JR
Six Stars

Re: Handle null value in key columns

If I understand correctly, you plan to have a composite key consisting of two columns, one of which can be NULL. Correct? As Oracle does not allow primary key columns to be NULL I assume you either have no primary key in your target table or it is using other columns. What you can do is to create an additional column in your target table holding the combination of key1 and key2 concatenated like this: target.key1 + (target.key2 == null ? "" : target.key2). Mark this one as your update key and it should work. You should also create a unique index on this column. Depending on the amount of records, this may speed up the update. Let the community know how this works out for you.
One Star

Re: Handle null value in key columns

Thanks JoRoesecke. I am pretty sure your approach would work. Unfortunately I could not change the table in DB. So wondering if there is anything in Talend could handle this. 
Ten Stars

Re: Handle null value in key columns

I'm pretty sure null == null doesn't evaluate to True in most databases.  If you can't update your table structure, you may want to break up your process into two updates: 
where key2 is not null, compare both key columns to the table values
where key2 is null, compare only key1 to the table value, with a static constraint on the table of "key2 is null".
A tMap component lets you create multiple outputs based on conditions.   Click on the green plus icon next to the wrench above the output to reveal the expression filter box.
One Star

Re: Handle null value in key columns

Thanks cterenzi! This should work. 
One Star

Re: Handle null value in key columns

Wait, this still does not solve the issue in tOracleOutput because I could not specify key2 is null.