topic Re: Handle null value in key columns in Talend Studio

Handle null value in key columns

Anonymous — Wed, 09 Nov 2016 22:22:28 GMT

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?

Re: Handle null value in key columns

Anonymous — Thu, 10 Nov 2016 02:41:03 GMT

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

Re: Handle null value in key columns

Anonymous — Thu, 10 Nov 2016 13:48:24 GMT

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?

Re: Handle null value in key columns

Anonymous — Mon, 14 Nov 2016 13:41:44 GMT

Anyone has any suggestion on this?

Re: Handle null value in key columns

JR1 — Mon, 14 Nov 2016 19:31:40 GMT

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.

Re: Handle null value in key columns

Anonymous — Mon, 14 Nov 2016 20:15:19 GMT

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.

Re: Handle null value in key columns

cterenzi — Mon, 14 Nov 2016 21:37:00 GMT

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.

Re: Handle null value in key columns

Anonymous — Tue, 15 Nov 2016 14:17:42 GMT

Thanks cterenzi! This should work.

Re: Handle null value in key columns

Anonymous — Tue, 15 Nov 2016 14:55:13 GMT

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