[resolved] tOracleOutput Update : recup the line which not updated
Hello,
I would like to update some data in a table. For that, I'm using a tOracleOutput component with the option Update and I check the "Use field option"
(key for update, field can be update) in "Advanced setting".
The schema is Inputfile --> tMap --> tOracleOutput
My job is ok but I would like to have the lines which not be update in the table.
Someone have an idea please ?
Thanks
Hi,
After the update (or before), you can do a LEFT OUTER JOIN using a tMap between the Oracle table (main) and the input file as the lookup. Use a schema that includes columns from both the table and the input file even if they are duplicated. For the records not found in the input, there will be a set of null columns.
These records can be filtered in a tFilterRow, routing the filter connection to the destination. The filtering criteria will be the presence of null values in the columns from the input file.
Don't do the tMap LEFT OUTER JOIN if you have a lot of data.
Stage the input file and use the Oracle MINUS set-based operation. This would provide query a tOracleInput. Oracle can give MINUS some performance boosts to this type of statement.
SELECT id, name, ...
FROM staged_file_table
MINUS
SELECT id, name
FROM target_table
If there was a record with id 10 in the staged_file_table that wasn't present in the target table, no update would have happened and the MINUS query would return the 10.
Sorry but I don't really see how I can do that. My staged_file_table is provide by the output of a tMap and I can use a tOracleInput to make a MINUS after...
