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