Hi,
I have a stored procedure, which returns me a result set which is an incremental snapshot.
It returns me a list of only continued products.
How do I perform a comparison from the last feed file with a new one, to see which all records are skipped and put them in the discontinued file list?
Any suggestions, help ?
Hi Karan,
I expect you were already able to call stored procedure so you have edge on which you have records from your database. There are at least 2 possibilities I can think of:
Most efficient would be to return only deleted records from stored procedure. In CloverETL there is concept of http://doc.cloveretl.com/documentation/ … ading.html but it can’t be used in DBExecute. But the concept is simple: keep some value describing last change in db (like timestamp or max id) and use it in stored procedure as input parameter. Then return just deleted records. So if you are lucky and your records are only ‘invalidated’ and not completely deleted, then this is best option.
Store last state of database in file (or db, …), for example by CloverDataWriter. Possibly you can limit file size by storing just ID or some identifier. Then in next run you can use DataIntersection for lost records detection - comparing last and current records. (similar concept used here viewtopic.php?f=4&t=3827&p=6307&hilit=dataintersection+upsert#p6307 for upsert)
I hope this helps.