I came up with an interesting Oracle-db problem and keep thinking about a feasible solution to solve this task. Lets say we got three tables (bold columns are PKs):
- A (Name, GroupID, versionID, Column1, Column2)
- B (Name, GroupID, versionID, Column1, Column2)
- C (versionID, date_created)
So it works as follows:
- When a new batch of data is imported into the db, each record is tracked with a new unique versionID in table C
- Table B holds all imports / changes while Table A only has one record (the newest)
What I'm trying to do right now, is get a sort of diff / changelog for the different batch uploads. This includes any differences in:
Name, GroupID, ColumnA, ColumnB, ColumnC between two specific dates.
I do this with two inner joins over A,B with Name&GroupID and then B and C over versionID and two dates. This works fine if the PKs Name & GroupID stay the same, see output 1:
|A.Name(new)|B.Name(old)|A.GroupID(new)|B.GroupID(old)|A.Column1(new)|B.Column1(old)|A.Column2(new)|B.Column2(old)|date_created(new)|date_created(old) |Test1 |Test1 |37 |37 |Dog |Cat |Mouse |Mouse |2015-08-10 |2015-08-20 | |Test3 | |38 | |Cat | |Mouse |2015-08-10 |2015-08-20 |New record | |38 | |Mouse | |Mouse | |2015-08-10 |
- output - A.Column1 changed from Cat to Dog
- output - a record gets deleted
- output - new record is added which did not exist before
My output works well for output scenario 1, however if the name or groupID gets changed / deleted, or a new record is insert this change obviously won't show up because the join does not work anymore. Does anyone have an idea to deal with this problem. My output should work as shown above in output 1 & 2 & 3. I am sadly not able to implement any triggers to the db and my goal is to try to solve this via sql queries.
Thanks in advance for your input