Currently using: R:BASE 2000 (version 6.5) Windows (32-bit), U.S. Version,
Build: 1.833xRT03
I'm having an ongoing problem with incorrect results from loops with a
cursor. When a matching value is not found for the WHERE statement, instead
of leaving a null value, the previous value of the variable is written into
the table. Obviously this causes problems. Sometimes they are not detected
until a lot of data has passed through.
In David Blocker's Advanced R:BASE programming class manual he advises to
avoid the WHERE CURRENT syntax in the UPDATE command. My (inherited) code
does use WHERE CURRENT. Could this be the problem? Since multiple updates
are being done, I'm not sure how to re-write the command. Would replacing
"WHERE CURRENT OF c1" with the WHERE statement from DECLARE CURSOR work?
David also advises against clearing any variables between the WHILE
statement and the ENDWHILE statement. So the only approach I see is to work
with the WHERE statement.
Advice from anyone who has dealt with this problem would be appreciated.
And please keep in mind that I'm a rookie so SPEAK SLOWLY AND CLEARLY. :-)
Thanks!
Cheryl
(All variables are declared)
-- Start WHILE loop 1
SET ERROR MESSAGES OFF
DROP CURSOR c1
SET ERROR MESSAGES ON
MAXIMIZE
DECLARE c1 CURSOR +
FOR SELECT notebook, msdate, instr, stdnum, runnum, analyte, stype +
FROM tmpmspec WHERE addflag = .vflagtest
OPEN c1
FETCH c1 +
INTO vnotebook INDICATOR ivnotebook, vmsdate INDICATOR ivmsdate, vinstr +
INDICATOR ivinstr, vstdnum INDICATOR ivstdnum, vrunnum +
INDICATOR ivrunnum, vanalyte INDICATOR ivanalyte, vstype INDICATOR ivstype
WHILE SQLCODE <> 100 THEN
SELECT ion_2, r_time2 +
INTO vion1 INDICATOR ivion1, vrtime1 INDICATOR ivrtime1 FROM autospec +
WHERE runnum = .vrunnum AND analyte = .vanalyte AND ionnum = 1 +
AND LIMIT = 1
SELECT ion_2, r_time2 +
INTO vion3 INDICATOR ivion3, vrtime3 INDICATOR ivrtime3 FROM autospec +
WHERE runnum = .vrunnum AND analyte = .vanalyte AND ionnum = 3 +
AND LIMIT = 1
SELECT ion_2, r_time2 +
INTO vion4 INDICATOR ivion4, vrtime4 INDICATOR ivrtime4 FROM autospec +
WHERE runnum = .vrunnum AND analyte = .vanalyte AND ionnum = 4 +
AND LIMIT = 1
UPDATE tmpmspec SET ion_1 = .vion1, r_time1 = .vrtime1, ion_3 = .vion3,+
r_time3 = .vrtime3, ion_4 = .vion4, r_time4 = .vrtime4 WHERE CURRENT OF
c1
IF vstype CONTAINS 'R' THEN
UPDATE tmpmspec SET stype = 'REC' WHERE CURRENT OF c1
ENDIF
IF vstype CONTAINS 'U' THEN
UPDATE tmpmspec SET stype = 'UNK' WHERE CURRENT OF c1
ENDIF
IF vstype CONTAINS 'S' THEN
UPDATE tmpmspec SET stype = 'STD' WHERE CURRENT OF c1
ENDIF
IF vstype CONTAINS 'M' THEN
UPDATE tmpmspec SET stype = 'MIS' WHERE CURRENT OF c1
ENDIF
FETCH c1 +
INTO vnotebook INDICATOR ivnotebook, vmsdate INDICATOR ivmsdate, vinstr +
INDICATOR ivinstr, vstdnum INDICATOR ivstdnum, vrunnum +
INDICATOR ivrunnum, vanalyte INDICATOR ivanalyte, vstype INDICATOR
ivstype
ENDWHILE
DROP CURSOR c1
