Great!! David
David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "Claudine Robbins" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Saturday, February 26, 2005 3:59 PM Subject: [RBG7-L] - Re: Subroutines in command files > More than that you helped me to see that I didn't really need the > incrementing code in two places. I simply ask the user to enter the NEXT > number to use as opposed to the LAST one used and this way I only increment > in the cursor loop. I also defined all my variables outside the cursor loop > and do not need to set whileopt off. Much tigher and cleaner code. > > Thanks again David :) > > > -----Original Message----- > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of David M. > > Blocker > > Sent: Saturday, February 26, 2005 2:37 PM > > To: RBG7-L Mailing List > > Subject: [RBG7-L] - Re: Subroutines in command files > > > > Glad I could help on both! > > > > David > > > > David Blocker > > [EMAIL PROTECTED] > > 781-784-1919 > > Fax: 781-784-1860 > > Cell: 339-206-0261 > > ----- Original Message ----- > > From: "Claudine Robbins" <[EMAIL PROTECTED]> > > To: "RBG7-L Mailing List" <[email protected]> > > Sent: Saturday, February 26, 2005 2:10 PM > > Subject: [RBG7-L] - Re: Subroutines in command files > > > > > > > Ah so! Very well. 1. Set WHILEOPT OFF it is... Thanks, I probably > > would > > > have been scratching my head all of next week... > > > > > > > -----Original Message----- > > > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of David M. > > > > Blocker > > > > Sent: Saturday, February 26, 2005 1:04 PM > > > > To: RBG7-L Mailing List > > > > Subject: [RBG7-L] - Re: Subroutines in command files > > > > > > > > Claudine > > > > > > > > When code TRACEs OK but crashes when you RUN, it usually means you are > > > > running with WHILEOPT set ON. You have two choices: > > > > > > > > 1. SET WHILEOPT OFF In your code > > > > 2. Keep WHILEOPT ON but check the code to make sure that all variables > > > > referenced in the WHILE loop are defined outside the loop: looking at > > your > > > > code, that's not now the case. > > > > > > > > David Blocker > > > > [EMAIL PROTECTED] > > > > 781-784-1919 > > > > Fax: 781-784-1860 > > > > Cell: 339-206-0261 > > > > ----- Original Message ----- > > > > From: "Claudine Robbins" <[EMAIL PROTECTED]> > > > > To: "RBG7-L Mailing List" <[email protected]> > > > > Sent: Saturday, February 26, 2005 1:17 PM > > > > Subject: [RBG7-L] - Re: Subroutines in command files > > > > > > > > > > > > > In executing this file, I am also encountering a weird problem. > > When > > I > > > > run > > > > > the file it stops after updating 3 records with the -ERROR- set > > error > > > > > command (3031) (I have no set error commands in the file...). On the > > > > other > > > > > hand, if I trace the file and 'execute to break' or 'execute to > > error', > > > > it > > > > > doesn't hiccup and correctly updates all 276 rows... Go figure? Is > > > > this > > > > a > > > > > full moon? <g> > > > > > > > > > > > -----Original Message----- > > > > > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of > > Claudine > > > > > > Robbins > > > > > > Sent: Saturday, February 26, 2005 11:54 AM > > > > > > To: RBG7-L Mailing List > > > > > > Subject: [RBG7-L] - Re: Subroutines in command files > > > > > > > > > > > > Hi David, > > > > > > > > > > > > Thanks for your offer to help. I'm building and incrementing an > > > > invoice > > > > > > number in a loop. In a batch process, I'm also updating an > > invoice > > > > number > > > > > > field in a table with a cursor loop. So I have a switch loop > > inside > > a > > > > > > cursor loop. Here is the code: > > > > > > > > > > > > DROP VIEW v_afelist NOCHECK > > > > > > CREATE TEMPORARY VIEW `v_afelist` + > > > > > > AS SELECT DISTINCT billing_id FROM d_b_allocation + > > > > > > WHERE da_invoice_dt = .vbatchdate > > > > > > > > > > > > --First time I create a number > > > > > > *(Number invoices) > > > > > > SET VAR vinvoiceid INTEGER = NULL > > > > > > SET VAR xmaxno TEXT = NULL > > > > > > SET VAR xstep2 TEXT = NULL > > > > > > SET VAR xlen TEXT = NULL > > > > > > SET VAR xprefix TEXT = NULL > > > > > > SET VAR xyear TEXT = NULL > > > > > > SET VAR xpad TEXT = NULL > > > > > > SET VAR vinvnbr TEXT = NULL > > > > > > *(First step, gotta start somewhere....) > > > > > > FILLIN vinvnbr USING 'Please Enter Last Invoice Number Used' > > > > > > SET VAR xmaxno = .vinvnbr > > > > > > GOTO makenumber > > > > > > > > > > > > *(UPDATE RECORDS with sequential invoice numbers through LOOP) > > > > > > LABEL update_rec > > > > > > > > > > > > DROP CURSOR c1 > > > > > > DECLARE c1 CURSOR FOR SELECT billing_id FROM v_afelist ORDER BY > > > > billing_id > > > > > > OPEN c1 > > > > > > FETCH c1 INTO vbillingid INDICATOR ivbillingid > > > > > > WHILE SQLCODE <> 100 THEN > > > > > > UPDATE d_b_allocation SET da_invoice_nb = .vinvnbr + > > > > > > WHERE billing_id = .vbillingid + > > > > > > AND da_invoice_dt = .vbatchdate > > > > > > *(Invoice number gets incremented by 1 each iteration) > > > > > > SET VAR xmaxno = .vinvnbr > > > > > > FETCH c1 INTO vbillingid INDICATOR ivbillingid --get next > > billing > > > > number > > > > > > --here is where I want to run my subroutine and come back to my > > cursor > > > > > > loop > > > > > > GOTO makenumber --create next invoice number > > > > > > ENDWHILE > > > > > > DROP CURSOR c1 > > > > > > > > > > > > This is my sub-routine > > > > > > LABEL makenumber > > > > > > *(Second step, extract the last four characters) > > > > > > SET VAR xstep2 = (SGET(.xmaxno,4,5)) > > > > > > *(Third step, make that value an integer) > > > > > > SET VAR xstep2 INTEGER > > > > > > *(Fourth step, increment to next number) > > > > > > SET VAR xstep2 = (.xstep2 + 1) > > > > > > *(Fifth step, back to a TEXT value) > > > > > > SET VAR xstep2 TEXT > > > > > > *(Sixth step, calculate how many zeros to pad with) > > > > > > SET VAR xlen TEXT = (SLEN(.xstep2)) > > > > > > SWITCH (.xlen) > > > > > > CASE 1. > > > > > > SET VAR xpad TEXT = '000' > > > > > > BREAK > > > > > > CASE 2. > > > > > > SET VAR xpad TEXT = '00' > > > > > > BREAK > > > > > > CASE 3. > > > > > > SET VAR xpad TEXT = '0' > > > > > > BREAK > > > > > > CASE 4. > > > > > > SET VAR xpad TEXT = ' ' > > > > > > BREAK > > > > > > ENDSW > > > > > > *(Seventh step, create new invoice number) > > > > > > SET VAR xprefix = 'D' > > > > > > SET VAR xyear = (SGET(CTXT(IYR4(.#DATE)),2,3)) > > > > > > SET VAR vinvnbr = (.xprefix + .xyear + '-' + .xpad + .xstep2) > > > > > > Sub-routine ends - Ideally, I would put this at the end of the > > code > > > > > > RETURN --to calling line > > > > > > > > > > > > RETURN --End of command file > > > > > > > > > > > > > -----Original Message----- > > > > > > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of > > David > > > > M. > > > > > > > Blocker > > > > > > > Sent: Saturday, February 26, 2005 11:29 AM > > > > > > > To: RBG7-L Mailing List > > > > > > > Subject: [RBG7-L] - Re: Subroutines in command files > > > > > > > > > > > > > > Claudine > > > > > > > > > > > > > > It's not clear from your email exactly waht you're trying to do. > > > > > > > > > > > > > > What do you mean by: > > > > > > > > > > > > > > Label first > > > > > > > Do 1 > > > > > > > Do 2 > > > > > > > Return ???? - doesn't work... > > > > > > > > > > > > > > Do you mean: > > > > > > > > > > > > > > Label First > > > > > > > RUN programname.RMD > > > > > > > RUN programnam.RMD > > > > > > > > > > > > > > ? > > > > > > > > > > > > > > If you send the actual code I think I can help! > > > > > > > > > > > > > > David Blocker > > > > > > > [EMAIL PROTECTED] > > > > > > > 781-784-1919 > > > > > > > Fax: 781-784-1860 > > > > > > > Cell: 339-206-0261 > > > > > > > ----- Original Message ----- > > > > > > > From: "Claudine Robbins" <[EMAIL PROTECTED]> > > > > > > > To: "RBG7-L Mailing List" <[email protected]> > > > > > > > Sent: Saturday, February 26, 2005 11:50 AM > > > > > > > Subject: [RBG7-L] - Subroutines in command files > > > > > > > > > > > > > > > > > > > > > > Hi all, > > > > > > > > > > > > > > > > I want to imbed some subroutines in a command file. > > > > > > > > > > > > > > > > Label first > > > > > > > > Do 1 > > > > > > > > Do 2 > > > > > > > > Return ???? - doesn't work... > > > > > > > > > > > > > > > > Label second > > > > > > > > Cursor loop > > > > > > > > Do 3 > > > > > > > > Do 4 > > > > > > > > Goto first > > > > > > > > End loop > > > > > > > > Return > > > > > > > > > > > > > > > > I can't find any documentation. I can do run labelfirst.rmd > > but > > I > > > > > > > really > > > > > > > > wanted to put all the code in one file. > > > > > > > > > > > > > > > > TIA, Claudine > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
