Hi Cheryl, Clearing the variable is not good but you can set it to null prior to the select statement. If nothing is found then it will remain null. I have used current of cursor and not had any problems, but I must admit that I rarely use it. You could also test the value of your indicator variables to see if data was found. It will equal 0 if it was not a null value, -1 if it was null.
Best regards, Mike Young On Fri, 28 Sep 2001 10:59:07 -0400, 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
