tuniquerow problem with duplicate rows

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Did you set the primary key in the schema?

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

[janhess]

But is it set on the database table?

[Anonymous]

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

[alevy]

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

[Anonymous]

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

[alevy]

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.