I have seen this happen, on occasion, as well. Use the statement:
Set var myvariable = null will reset the variable to a null value, and avoid carry-over. Place this statement just before the lower "fetch" Lee Bailey Bailey & Associates E-Mail: [EMAIL PROTECTED] Phone: 954-659-1780 Fax: 954-659-1781 ----- Original Message ----- From: "McClure, Cheryl" <[EMAIL PROTECTED]> To: "Rbase-L (E-mail)" <[EMAIL PROTECTED]> Sent: Friday, September 28, 2001 10:59 AM Subject: Carry over variables in loops with cursors > 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 >SET VAR whatever = null > 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
