Cheryl This is not my forte either, but from what I have an understanding to date you can set the all the variables needed back to null just before the next cursor assignment under the way most do cursor loops. The way yours is written looks like you could do it either before the SELECTS, or just after the FETCH statement.
Jim Limburg "McClure, Cheryl" wrote: > > 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
