tuniquerow problem with duplicate rows

One Star

tuniquerow problem with duplicate rows

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

Re: tuniquerow problem with duplicate rows

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
One Star

Re: tuniquerow problem with duplicate rows

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

Re: tuniquerow problem with duplicate rows

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
One Star

Re: tuniquerow problem with duplicate rows

Did you set the primary key in the schema?

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

Re: tuniquerow problem with duplicate rows

But is it set on the database table?
Highlighted
One Star

Re: tuniquerow problem with duplicate rows

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).
Seven Stars

Re: tuniquerow problem with duplicate rows

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

Re: tuniquerow problem with duplicate rows

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

Re: tuniquerow problem with duplicate rows

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.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch