Marc, try adding a USING clause to the DELETE DUPLICATES command, and
specify the columns to be used for the duplicate check.
Marc wrote:
Hi
Out of 100 offices this has happened only about 6 times in a year. I
think it is from
computer hiccups but I am wanting to make my code as tight as possible.
Basically we put charges and payments in a Tran_daily file. At the
end of the day
if everything is correct we Close the day, do some updates, move the
charges and
payments to the Tran_Hist table and summaries the totals in a Recap
table with the
date on it, then delete the Tran_daily rows.
About 90% of the rows from the Tran_daily table got copied (inserted)
into the Tran_Hist
table twice for 2/8/08. This office has used my app for 10 years and
this is the first time
it has happened to them. I told them if the same code works 99% of
the time and one day
screws up it is not the code/RBase it is a computer quirk.
Below is a summary of the code, is there a better way to do this or
trap for errors?
Sorry this is so long
Marc
DELETE DUPLICATES FROM tran_daily --so this should have stopped the
dups in the Hist
SET VAR vdaycnt INTEGER = NULL
SELECT COUNT (*) INTO vdaycnt FROM tran_daily
SET VAR vhistcnt INTEGER = NULL
SELECT COUNT (*) INTO vhistcnt FROM tran_hist
-- copy tran_daily TO tran_hist
INSERT +
INTO tran_hist (custnum,date ......... +
................,modf4 FROM tran_daily
SET VAR vhistcnte INTEGER = NULL
SELECT COUNT (*) INTO vhistcnte FROM tran_hist
IF vhistcnte <> (.vdaycnt + .vhistcnt) THEN
PAUSE 1 USING 'Some data was not copied to the History file '
ENDIF
if this poped up they didn't remember it
*(insert RECAP FILE WITH TOTALS FOR DAY)
INSERT INTO recap (............ , tptpay,+
VALUES (.vdate, ................ '1',.vtvisa)
DELETE ROWS FROM tran_daily
SET VAR vrecapchar CURRENCY = 0.00
SET VAR vrecappay CURRENCY = 0.00
SET VAR vrecapchar CURRENCY = tcharges IN recap WHERE COUNT = LAST
SET VAR vrecappay CURRENCY = tpayment IN recap WHERE COUNT = LAST
IF tcharge = .vrecapchar AND totpay = .vrecappay THEN
PAUSE 1 USING 'Finished Recap Created' ICON confirm ....
ELSE
PAUSE 1 USING +
'Recap DID NOT match Check the Recap and use a Backup if necessary' +
ICON SERIOUS BUTTON 'OK' OPTION BACK_COLOR 65535 | ...+
if this poped up they didn't remember it
ENDIF
RECALC VARIABLES
RETURN
