AnsweredAssumed Answered

SQL statement advice

Question asked by kiwikaty on Nov 24, 2016
Latest reply on Nov 27, 2016 by philmodjunk

Hi there


Each night I need to reconcile around 70,000 records and once a week 315,000 records. At the moment I have a unstored calc which compares about 10 fields between the two tables to see if there are any differences eg.

if (file1 field A not equal to file2 field A; 1; 0) +

if (file1 field B not equal to file2 field B; 1; 0) +

if (file1 field C not equal to file2 field C; 1; 0) +...


Across the massive record set I then do a replace on a local field of the unstored calc value into an indexed number field - I then do a find on this indexed field for > 0 and then with this found set (which is every record where the data in file1 does not match something in file2) I do an import into file2 using "update matching records".


The thing is the replace and then import is taking hours each night. Initially I was doing an import matching on all but then that did not make sense to import ones that did not need updating and I did not like how many records were being touched.


I need to claw back some of the time this process is taking so wondering if I could somehow use SQL instead inside FM to find all the ones that have differences. The import will still be slow but marking ones that need importing may be quicker.


I was just wondering if someone who knew FM and SQL knew what sort of statement I could use to locate the set that needs updating maybe without having to store the unstored calc first? Or maybe these is just a whole better way of approaching this nightly task?


Any advice appreciated.


Katy B