tDBoutput : identify lines which are not taken into account further to a sql query from a csv file

[android1686764069]

Hello

I have problems with something related to SQL with talend.

As an input, I have several lines to update using SQL (oracle table) and I need to identify lines in my input which were not updated for any reasons.

To update the table, I use tDBoutput and a csv file with the lines I want to update.

here is an example of the csv file (see pic1)

Here is an example of my table (see pic2).

For example, in the csv file, one ID does not exist in the table (line 5 with id 5338070, in yellow), sot it can’t be updated in the table as the ID not found.

This line should be written in an output file

I tried a job like in the pic 3, but the output file is empty.

If replace the reject ilne by a main line, I find all the lines from the input written in the outpuf file, which is weird because one is not supposed to be updated.

How can I identify lines which are not updated?

[anselmopeixoto]

Hi @android devops​ 

The lines are not being rejected because the database does not generate an error for an update command that does not match any rows to update.

The reject output would function for errors triggered by the database, such as a foreign key constraint not being found.

To identify all the lines that cannot be updated because they do not exist in the output table, I would utilize a tMap and perform a lookup on the same destination table. This tMap would have two outputs: one for the matching rows that would be sent to tDBOutput, and another for the unmatched rows that would be directed to a file.

[android1686764069]

Hi

I'm' confused. In the example I gave, the ID is supposed to be key which is not found in the table.

So it should trigger an reject output shouldn't it?

[Anonymous]

@android devops​ , the problematic rows will be rejected on tDBOutput, the row with ID ​5338070 is not found in target table and it will be not updated, but it is not a problematic data. As @Anselmo Peixoto​ suggested, use a tMap to an inner join based on ID column between source file and target table, generate two output tables, one for the matched rows, and another one for the unmatched rows.