Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table looking roughly like below which I read into QVD every morning. My problem is that sometimes the field AMOUNT is changed and I have no modification flag that show it. How can I do a insert, update, delete load ?
Before change:
Case_id AMOUNT Date Function_code UserID RowID
a 523,33 01-06-2012 DB 55 4
After change:
Case_id Amount Date Function_code UserID RowID
a 215,60 01-06-2012 DB 55 4
I only want to load and keep the latest value. Any ideas ?
Since there is no way to determine what actually is the latest value what you want is not possible. You really will need a field that indicates that a record has changed.
ok, I think this may be possible, however it's a fairly horrible solution.
1 - load from your qvd, and include an additional field, a concatenation of Case_id and Amount.
2 - use a concat to concatenate a comma separated list of all possible values of the new concatenated field
3 - using the concat string from step 2, query your database for any records where the concatenation of Case_id and Amount isn't in your list.
4 - Concatenate on to this table from your existing qvd 'where not exists(RowID)'
As I said, it's horrible.
A better solution would be to add a new modified date field to your source table, and set up a trigger to populate it on insert or update.