Note: I am providing the example of my issue. Experts, please help me on this problem.
I have a Tables SALES, SALES_DELETED in SQL Server.
SALES_TABLE
ID
SALES_AMNT
INSERTED TIME
UPDATED TIME
1
1000
12/23/15 1:00
12/23/15 1:00
2
1500
12/23/15 1:00
12/23/15 1:00
3
2000
12/23/15 1:00
12/23/15 1:00
4
5000
12/23/15 2:00
12/23/15 2:00
5
500
12/23/15 2:00
12/23/15 2:00
6
1000
12/23/15 2:00
12/23/15 2:00
7
1500
12/23/15 2:00
12/23/15 2:00
8
2000
12/23/15 3:00
12/23/15 3:00
9
5000
12/23/15 3:00
12/23/15 3:00
10
500
12/23/15 3:00
12/23/15 3:00
SALES_DELETED:
ID
DELETED TIME
FLAG
11
12/23/15 1:00
Y
12
12/23/15 1:00
Y
13
12/23/15 1:00
Y
14
12/23/15 2:00
Y
15
12/23/15 2:00
Y
In database, triggers are available on SALES_TABLE.
Trigger 1: Inserted&Updated - Displays default SYSDATE for inserted and updated records (For newly inserted records, INSERTED TIME and UPDATED TIME is same, whereas UPDATED TIME vary for updated records)
Trigger 2: Deleted - Deleted records physically deletes from SALES_TABLE and Added in SALE_DELETED Table.
Here i have to perform INCREMENTAL LOAD for SALE_TABLE. Please find the attached script.
Triggers are running every one hour. My Incremental load script is working fine when i reload QVW after triggers are performed on SQL Server.
Whereas if i reload my QVW simultaneously when triggers are running, then i am not receiving the deleted records into deleted QVD and they are not removing from mail QVD.
When i reload next time, i will be getting deleted records from max time of last road to current time. I am missing some of deleted records before max time of last road.
Now my main QVD contains some of deleted records as well.
My present solution is to reload the QVW after triggers performed on SQL.
However i will get this issue in long run.
Can anyone suggest permanent solution for this problem. It will be very helpful to me.
1 - when pulling the last execution datetime, maybe you should also pull the max DELETED TIME from DELETED QVD. This can then be used for the incremental extract from the SALES_DELETED table.
2 - I think your merge of DELETED QVD and SALES QVD is wrong. You may have the same record in both. The one coming from SALES QVD will have a FLAG of N, whereas the one from DELETED QVD will have a RecordDeleteStatus of Y.
Instead you should use a MAPPING load from DELETED QVD or your SALES_DELETED table.
When you load SALES QVD, use ApplyMap('MappingTable', ID, 'N'). If this is N, then it's not a deleted record.
When pulling the last execution datetime, maybe you should also pull the max DELETED TIME from DELETED QVD.
(If deleted records are ZERO then it will create a problem... means in the existing deleted QVD had zero records then there is no time time information in this QVD)
Any solution for this?
About point 2:
I incorrectly mentioned in the script about FLAG.
The one coming from SALES QVD will have a FLAG of N, and the one from DELETED QVD will have a FLAG of Y only. So finally i am filtering with where condition.
the solution for point 1 is maybe if you check if not isnull(qvdcreatetime('DELETED QVD'))
then retrieve the last DELETED TIME, otherwise manually set it, maybe with makedate(1900,1,1)
Regarding point 2, you are correct. The fact that you are first loading from DELETED QVD, then concatenating from SALES QVD where not Exists(ID) should deal with this correctly.
Another point though. You seem to have correctly dealt with incrementing your SALES QVD but not your DELETED QVD. You really should do this as if you don't then records which have been deleted in one load will not appear in the next load. This means that they still exist in SALES QVD, and so will reappear in your final table.
For only deleted records, i do filter based on makedate (which filters every time all records with all deleted QVD, however it may take more time for reloading or i go for today()-1 like that based on reload time).
I hope it will work out even if the Triggers on database run on same time. However i will try to avoid that time to reload my QVW.
Thank you for your valuable suggestion and time to analyze my issue.
