Cheryl, I took a quick look at your file (too quick to be sure <g>)...
Does the code below do what you're looking for?? Ben Petersen ------------------------------------------------------------------------- Update tmpMSpec + SET ion_1 = ion_2, rtime1 = rtime2 + whe addflag = .vFlagTest and ionum = 1 Update tmpMSpec + SET ion_3 = ion_2, rtime3 = rtime2 + whe addflag = .vFlagTest and ionum = 3 Update tmpMSpec + SET ion_4 = ion_2, rtime4 = rtime2 + whe addflag = .vFlagTest and ionum = 4 UPDATE tmpmspec SET stype = 'REC' WHERE stype = 'R' UPDATE tmpmspec SET stype = 'UNK' WHERE stype = 'U' UPDATE tmpmspec SET stype = 'STD' WHERE stype = 'S' UPDATE tmpmspec SET stype = 'MIS' WHERE CURRENT 'M' --------------------------------------------- On 28 Sep 2001, at 10:59, 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 >
