Thank you David. This is the whole code. I had two command files originally and it worked fine. I was just trying to find a way to combine the two without having the code repeated twice and your option 1 is what I ended up with, run the switch loop once and then put it in the cursor loop again to run there.
Now, if I could only get it to run without that pesky error.... > -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of David M. > Blocker > Sent: Saturday, February 26, 2005 12:28 PM > To: RBG7-L Mailing List > Subject: [RBG7-L] - Re: Subroutines in command files > > Claudine > > You are confusing what the function of the RETURN command is - RETURN does > NOT mean END THIS LABEL code and go back to the line after the GOTO > command. > It means END this PROGRAM and return to the PROGRAM that called it. > > Since I'm not sure I'm seeing the whole program, I'm not sure what's best > for you here. Here are three possible options:: > > 1. Remove the GOTO and LABELS: Just move the code up where you want it to > run, like this, starting with the FILLIN: > > FILLIN vinvnbr USING 'Please Enter Last Invoice Number Used' > SET VAR xmaxno = .vinvnbr > > *(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 > > 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 > > 2. Remove the labels and put the code into a separate program: > > Program1: > > FILLIN vinvnbr USING 'Please Enter Last Invoice Number Used' > SET VAR xmaxno = .vinvnbr > > RUN PROGRAM2 > > 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 > RETURN > > > PROGRAM2: > > *(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 means go back to the line in the calling program after the RUN > -- PROGRAM2 command > RETURN > > > 3. Use GOTO Label but this way: Notice all I've done in this one is to > REPLACE your RETURN command at the end of the MakeNumber code with a GODO > Update_Rec so it will go back to the line of code where you want it > continue. > > *(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 > GOTO Update_Rec > > RETURN --End of command file > > > Hope this helps! > > 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 12:54 PM > 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 > > > > > > > > > > > >
