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?
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?
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.
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.
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.
