Karen, Have you explored using a SWITCH inside the cursor? It might be faster than GOTO.
Jan -----Original Message----- From: "jan johansen" <[email protected]> To: "karentellef via RBASE-L" <[email protected]> Date: Fri, 28 Apr 2017 11:49:19 -0700 Subject: Re: [RBASE-L] - From The Edge: R:BASE Cursors Explained My apologies. I read "rows" as "rows of code" not "rows of data" -----Original Message----- From: karentellef via RBASE-L <[email protected]> To: [email protected] Date: Fri, 28 Apr 2017 14:45:13 -0400 Subject: Re: [RBASE-L] - From The Edge: R:BASE Cursors Explained Yes, exactly. Ifyour cursor is on data row #10, and for some reason you wanted the cursor to skip down to data row #15, you could issue a FETCH RELATIVE 5 and it would jump 5 data rows. My cursor would never do anything other than fetch the next row. I never skip rows, back up, or jump forward. I remember using FETCH RELATIVE once in my career, way back when I did a program to randomly select a row of data from a table. I would use the RANDOM function (whatever that was) to generate a random number, and then within my cursor would use that to skip forward or backward that random number of data rows. Karen -----Original Message----- From: jan johansen <[email protected]> To: karentellef via RBASE-L <[email protected]> Sent: Fri, Apr 28, 2017 1:37 pm Subject: Re: [RBASE-L] - From The Edge: R:BASE Cursors Explained Karen, I have not used scroll so don't have any experience with it. However, this command seems to indicate "skipping" around RELATIVE n - Moves the cursor the specified number of rows from the current cursor position. This option moves the cursor either forwards or backwards - forwards if a positive number is specified, backwards if a negative number is specified. The intervening rows are jumped over. You can't jump past the last row or the first row; an "end of data" error is returned if the specified number would take you past the beginning or end of the selected rows. Jan -----Original Message----- From: karentellef via RBASE-L <[email protected] [mailto:[email protected]]> To: [email protected] [mailto:[email protected]] Date: Fri, 28 Apr 2017 14:25:08 -0400 Subject: Re: [RBASE-L] - From The Edge: R:BASE Cursors Explained Jan, I think you're misunderstanding what SCROLL is. What that does is to scroll through rows of data in your cursor, not through lines of code. So your declare cursor could FETCH LAST to go to the last data row, FETCH RELATIVE 5 to jump forward by 5 rows of data, etc. FETCH NEXT is the only possible use in my situation, but after issuing the FETCH NEXT you still have to go somewhere with the data, I'm not just fetching and dumping out of the loop as the pdf example shows. Unless I'm misunderstanding what you were suggesting. You seem to be suggesting that SCROLL would help jump around the program code.... Karen -----Original Message----- From: karentellef <[email protected] [mailto:[email protected]]> To: rbase-l <[email protected] [mailto:[email protected]]> Sent: Fri, Apr 28, 2017 1:18 pm Subject: Re: [RBASE-L] - From The Edge: R:BASE Cursors Explained 1. The programmer was good at initializing variables before the cursor. Here's his list (look at all the REAL datatypes, the database is loaded with REAL columns). In 2014 when our While loop first started misbehaving, I made sure that no variables are defined using a datatype within the looping, even tho we do run with whileopt OFF. SET VAR vagentno TEXT = NULL, vagentcount INTEGER = 0, vagent1comm REAL = NULL, + vagent2comm REAL = NULL, vagent3comm REAL = NULL, vagentcomm REAL = NULL, + vtestagtcomm REAL = NULL, vagtstatus TEXT = NULL, vrktcomm REAL = NULL, + vrktcommission REAL = NULL, vagentcommission REAL = NULL, vplanins TEXT = NULL, + vcountmgr INTEGER = 0, vcountpol INTEGER = 0, vinsadvance REAL = 0, + vtestrecdate DATE = NULL, vtestagtcomm REAL = NULL, vagtcomm REAL = NULL, + vcount INTEGER = 0, vpaycode TEXT = NULL, vicount INTEGER = 0, vagtadvance REAL = NULL, + tdate DATE = NULL, fac_year INTEGER = NULL, fac_month INTEGER = NULL, viscancelled TEXT = NULL, + vdate1 DATE = NULL, vdate2 DATE = NULL, vdatecursor DATE = NULL, vdays INTEGER = NULL, + vmonths INTEGER = NULL, vfreqtx TEXT = NULL, vfreq INTEGER = NULL, vmonprem CURR = NULL 2. Most ofthe "gotos" (7 of them) relate to going to separate areas within the loop, to do certain calculations, skip over some calculations, or to return back to a previous spot in the loop if something needed to be repeated. There is a possibility with very careful reconstruction that I could avoid a few of those gotos by constructing yet more nested IF loops, not sure. It's one thing that's on my list to try! There are only 3 actual "goto startloop" that relate to going back to the top to get another record, which is what you could use the SCROLL for. I've never tried "scroll" without a "while" loop, however, so I'd have to test if that would even work. When this was an actual while loop, I think it used "CONTINUE", which would be my first choice over SCROLL, but I no longer have a while loop. The "goto startloop" is only evaluated if there's error conditions in the record, and is only at the very top of the processing loop. If I go with one of my ideas of setting up an autonumbered temp table first rather than a declare cursor, I should be able to do the error checking before I even get into the loop, so that will take away the "goto startloop" altogether! Karen -----Original Message----- From: jan johansen <[email protected] [mailto:[email protected]]> To: karentellef via RBASE-L <[email protected] [mailto:[email protected]]> Sent: Fri, Apr 28, 2017 11:43 am Subject: Re: [RBASE-L] - From The Edge: R:BASE Cursors Explained Karen, This is an excellent article. A couple of things for you. 1. Since you are dealing with an OLD cursor, make sure you SET all the needed VARIABLE datatypes and values to NULL completely outside the CURSOR. You can set the values inside the CURSOR but you can't (shouldn't) really define vars inside the CURSOR. 2. Because of all your GOTO's inside the CURSOR, you should really explore the SCROLL feature in your cursor. The cursor is looking for the GOTO Labels from top to bottom repeatedly. Telling the cursor to go forward or backward a certain number of rows (since you know where the labels are) would most certainly speed up the cursor. Jan -----Original Message----- From: karentellef via RBASE-L <[email protected] [mailto:[email protected]]> To: [email protected] [mailto:[email protected]] Date: Fri, 28 Apr 2017 12:16:47 -0400 Subject: Re: [RBASE-L] - From The Edge: R:BASE Cursors Explained Cool, read the whole document!! Unfortunately not alot of it applies to my specific situation since there are no nested cursors and it's never updating any tables. The original writer of the program also did a nice job of writing the initial "declare" command to pull in as much data as needed, including some calculations right there rather than within the loop. But one thing jumped out, that you say that a "set var" is slightly faster than a "select into". I was thinking of converting all those "set var" statements, so I won't bother now! Karen -----Original Message----- From: A. Razzak Memon <[email protected] [mailto:[email protected]]> To: rbase-l <[email protected] [mailto:[email protected]]> Sent: Fri, Apr 28, 2017 10:56 am Subject: [RBASE-L] - From The Edge: R:BASE Cursors Explained Friday, April 28, 2017 >From The Edge.: R:BASE Cursors Explained Product.......: R:BASE X, R:BASE X Enterprise (Version 10) Build.........: 10.0.2.20411 or higher www.rupdates.com [http://www.rupdates.com/] Keywords......: Commands, CURSOR, DECLARE, FETCH, SELECT An R:BASE cursor is a valuable programming control structure that enables traversal reference over the records in a database. It is a pointer to rows in a table that can step through rows one by one, performing the same action on each row. A cursor can be set to point to all the rows in a table or to a subset of rows. A cursor is set using the DECLARE CURSOR command. R:BASE supports several types of cursors and cursor structures including: . Multi-Table Cursors . Non-Updateable Cursors . Nested Cursors . Resettable Cursors . Scrolling Cursors All of the cursors above and examples of Optimizing Cursors are described within the R:BASE Cursors Explained technical document. >From The Edge: http://www.razzak.com/fte [http://www.razzak.com/fte] Article Title: R:BASE Cursors Explained Very Best R:egards, Razzak. www.rbase.com [http://www.rbase.com/] www.facebook.com/rbase [http://www.facebook.com/rbase] -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] [mailto:[email protected]]. For more options, visit https://groups.google.com/d/optout [https://groups.google.com/d/optout]. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] [mailto:[email protected]]. For more options, visit https://groups.google.com/d/optout [https://groups.google.com/d/optout]. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] [mailto:[email protected]]. For more options, visit https://groups.google.com/d/optout [https://groups.google.com/d/optout]. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] [mailto:[email protected]]. For more options, visit https://groups.google.com/d/optout [https://groups.google.com/d/optout]. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] [mailto:[email protected]]. For more options, visit https://groups.google.com/d/optout [https://groups.google.com/d/optout]. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] [mailto:[email protected]]. For more options, visit https://groups.google.com/d/optout [https://groups.google.com/d/optout]. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] [mailto:[email protected]]. For more options, visit https://groups.google.com/d/optout [https://groups.google.com/d/optout]. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
