Cheryl: I had the WHERE CURRENT OF CURSOR in my code until a few years ago, when it stopped working with a version change. I have not tested it since. It is handy and hard to replace. If it is working, I would leave it.
To replace it, you will need to be able to positively identify the current row with data in variables. The WHERE condition on the DECLARE CURSOR statement is too generic to id a row. You may need to select more columns for this purpose. If the fields that identify the rows may have null values, it gets difficult building code that will work with them. fieldname = value works when not null fieldname is null is needed when it is null It looks like some of the UPDATES are doing a generic change that could be moved outside the WHILE loop, and greatly reducing the number of commands executed. Instead of this inside the while loop: > IF vstype CONTAINS 'U' THEN > UPDATE tmpmspec SET stype = 'UNK' WHERE CURRENT OF c1 > ENDIF why not this before or after it: UPDATE tmpmspec SET stype = 'UNK' WHERE stype = 'U' Hope this helps. Jim Blackburn Kodiak I also would rewrite the fetch: FETCH c1 INTO vnotebook i1, vmsdate i1, vinstr i1, vstdnum i1,+ vrunnum i1 ivrunnum, vanalyte i1, vstype i1 The INDICATOR text is optional - removing it improves readability. If the indicator variables are not used, there is no need to have a lot of different, long variable names. "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
