topic Re: [resolved] tOracleOutput Update : recup the line which not updated in Talend Studio

[resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Sat, 16 Nov 2024 12:32:20 GMT

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

Re: [resolved] tOracleOutput Update : recup the line which not updated

janhess — Wed, 30 Nov 2011 16:07:21 GMT

use insert or update?

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Wed, 30 Nov 2011 16:21:24 GMT

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.

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Wed, 30 Nov 2011 16:32:11 GMT

Yes normally, I use a lookup but in this case, the main Oracle table have 58 705 389 lines so it will take so much time.

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Wed, 30 Nov 2011 17:13:38 GMT

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.

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 10:25:00 GMT

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

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 12:07:16 GMT

Hi,
Can you post a picture of your job?

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 13:06:54 GMT

My suggestion about the MINUS operator requires a new staging table in Oracle to take advantage of the in-database processing.

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 13:08:15 GMT

How I can post a picture ?

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 13:15:06 GMT

....(T_T). Just click on the post reply button on the top of the topic and select the number of the picture you want to upload...

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 13:17:04 GMT

ok thanks

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 13:18:19 GMT

I would like to update the table "MARQ" with the data from the tMap ...

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 14:26:46 GMT

Tu peux upload le job entier parceque la je vois quasi rien, merci !

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 14:52:23 GMT

Ben la partie du job en question est le tMap4 ainsi que le tOracleOutput "MARQ" . Le reste du job fais juste quelques contrôles...

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 15:05:33 GMT

Et en fait c'est quoi exactement ton probleme? T'as google talk?

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 15:08:50 GMT

Quand tu dis que tu veux les lignes qui ne seront pas updatées, tu parles des enregistrements qui n'existent pas encore, c'est à dire des enregistrements qui seront à terme insérés?

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 15:23:39 GMT

Je dois mettre a jour des données d'une table Oracle en fonction d'un fichier source fourni. Je souhaiterai identifier les lignes de mon fichier source qui n'ont pas été mise à jour dans la table (pb de clé donc).
non dsl, je n'ai pas google talk ...

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 15:42:19 GMT

Comment fais tu ton update? Est ce que tu fais une jointure sur ta table au préalable?
ttable
|
tinputfile-----|-----ttable

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Thu, 01 Dec 2011 15:43:38 GMT

Je ne vois pas vraiment quel probleme tu pourrais avoir sur tes clefs à partir du moment ou les enregistrements existent deja, tes clefs ne bougent donc pas.

Re: [resolved] tOracleOutput Update : recup the line which not updated

Anonymous — Fri, 02 Dec 2011 09:36:08 GMT

Or3l,
A la base, en faisant un tOracleOutput , mes lignes sont mises à jour mais je ne récupère pas les lignes qui n'ont pu être mise à jour.
La j'ai résolu en utilisant le conseil de Walkerca. Pour cela, j?écris d'abord mes lignes dans une table temp Oracle. Ensuite avec un tOracleInput, je fais un left join sur la table source avec mes champs key :
SELECT A.t1, A.t2, A.t3, B.key, B.key2
FROM A
left outer join B
ON A.t1 = B.key and A.t2 = B.key2;
Si key et key2 sont nul, c'est que ma ligne source n'existe pas dans ma table de destination. je peux ensuite, à l'aide d'un tMap ou je test la nullité de ces 2 champs, identifier les lignes qui n'ont pas pu être mise à jour dans ma table.