topic Re: tuniquerow problem with duplicate rows in Talend Studio

tuniquerow problem with duplicate rows

Anonymous — Tue, 28 Aug 2012 09:40:24 GMT

hi everybody,
I'm attempting to take unique rows from a set of files (where rows can appear in multiple files) and after I want to insert them in my database table. The problem is that rows are duplicated. See the screen shots.
thanx...

Re: tuniquerow problem with duplicate rows

Anonymous — Tue, 28 Aug 2012 10:00:35 GMT

The tUniqRow only checks the current iteration (file).
2 solutions I can think of:
- merge all files into a single file and use tUniqrow afterwards
- use Update or Insert on the SQL component

Re: tuniquerow problem with duplicate rows

Anonymous — Tue, 28 Aug 2012 10:46:52 GMT

I already tryed the "update or insert" strategy but it doesn't work (or It seams that I don't understand how it works). I have used the toracleoutput component and Action on data I selected "Update or insert" but I get errors:
ORA-01747: invalid user.table.column, table.column, or column specification
And even that I have duplicated rows.

PS: How to merge files comming from tfilelist???
Thanx...

Re: tuniquerow problem with duplicate rows

Anonymous — Tue, 28 Aug 2012 13:02:25 GMT

I already tryed the "update or insert" strategy but it doesn't work (or It seams that I don't understand how it works). I have used the toracleoutput component and Action on data I selected "Update or insert" but I get errors:
ORA-01747: invalid user.table.column, table.column, or column specification
And even that I have duplicated rows.

Did you set the primary key in the schema?

PS: How to merge files comming from tfilelist???
Thanx...

by creating a new file using the "Append" option in tFileOutputDelimited

Re: tuniquerow problem with duplicate rows

Anonymous — Tue, 28 Aug 2012 13:52:20 GMT

Did you set the primary key in the schema?

Yes I did. Find attached the screen shot of the t_oracleoutput schema.

Re: tuniquerow problem with duplicate rows

janhess — Tue, 28 Aug 2012 15:45:21 GMT

But is it set on the database table?

Re: tuniquerow problem with duplicate rows

Anonymous — Tue, 28 Aug 2012 18:23:51 GMT

Hi,
The proposed solution should work, but here's what I sometimes do (if the files are not too big/there are not so many files and if you expect quite numerous duplicates or if I need to apply a specific treatment (log?) on them) : use a tHashOutput to "join" your data and use the tUniqRow after the corresponding tHashInput.
You might need to ask to show these components (Designer>Palette Settings in project parameters).

Re: tuniquerow problem with duplicate rows

alevy — Wed, 29 Aug 2012 06:03:56 GMT

Try tFileList --> tFileInputDelimited --> tUnite --> tUniqueRow --> tOracleOutput.

Re: tuniquerow problem with duplicate rows

Anonymous — Wed, 29 Aug 2012 19:37:37 GMT

Thanx it's OK but I still have the following error:
ORA-01747: invalid user.table.column, table.column, or column specification

Re: tuniquerow problem with duplicate rows

alevy — Thu, 30 Aug 2012 02:34:13 GMT

I think you can't use "update or insert" if you don't have at least one non-key column i.e. the key columns become the WHERE clause and the non-key columns become the SET clause of the UPDATE statement.