|
Ahh... so you make sure everything else works before even fetching. Do you really split your SQL stuff into subroutines? I think I tried it once and it wouldn't work. It wanted them all "in order" in the same section of code... Brad > -----Original Message----- > From: Phil [mailto:sublime78ska@yahoo.com] > Sent: Friday, May 04, 2001 3:00 PM > To: RPG400-L@midrange.com > Subject: RE: SQL, the best way for looping > > > I check sqlcod = 0 for connect, disconnect, open, close, > declare, prepare > etc. > > With Fetch and select I check for SQLCOD = 100. > > Something like > > Dou eof > Exsr SQLFetch > If eof = *on > Leave > Endif > Enddo > > SQLFetch begsr > Fetch > If sqlcod = 100 > Eval eof = *on > Endif > Endsr > > Phil > -----Original Message----- > From: owner-rpg400-l@midrange.com > [mailto:owner-rpg400-l@midrange.com]On > Behalf Of Stone, Brad V (TC) > Sent: Friday, May 04, 2001 3:35 PM > To: 'RPG400-L@midrange.com' > Subject: SQL, the best way for looping > > This brings up an interesting question. What do people use > for looping in > SQL. I know that if you check for 100, if your cursor > doesn't open for some > reason you will loop forever, as Bruce mentioned. > > What is the "best" way to do this? > > Brad > > > -----Original Message----- > > From: R. Bruce Hoffman, Jr. [mailto:rbruceh@attglobal.net] > > Sent: Friday, May 04, 2001 2:16 PM > > To: RPG400-L@midrange.com > > Subject: Re: SQL - just looking for a little guidance. > > > > > > Not a comment on the SQL, but why do you first test not equal > > to 100 and > > then test equal to zeros on the SQLCODE? > > > > If you are looking for efficiency, those tests don't make a > > whole lot of > > sense. > > > > Also be aware that since your loops are based on the SQLCODE > > being 100, that > > another serious error returning something other than 100, > > will cause the > > program to loop. > > > > =========================================================== > > R. Bruce Hoffman, Jr. > > -- IBM Certified Specialist - AS/400 Administrator > > -- IBM Certified Specialist - RPG IV Developer > > > > "America is the land that fought for freedom and then > > began passing laws to get rid of it." > > > > - Alfred E. Neuman > > > > -----Original Message----- > > From: Smith, Mike <Mike_Smith@RGCResources.com> > > To: RPG400-L@midrange.com <RPG400-L@midrange.com> > > Date: Friday, May 04, 2001 9:46 AM > > Subject: SQL - just looking for a little guidance. > > > > > > >I would just like for you to look at this program, and tell > > me if it should > > >have been done differently. > > >It doesn't do a whole lot, but i would like to make it as > > efficient as > > >possible. > > >Should i have done a multirow fetch? > > >could i have done a join on the update and made the whole > > thing work in 1 > > or > > >2 sql statements.? > > > > > >I'm just trying to figure out how to do things better. > > > > > >Oour shop is exploring SQL and trying to use it as much as > > possible and i'm > > >the guy that gets to experiment with it. So any help is > appreciated. > > > > > >I have this program that updates 2 files based on the data > > from the first > > >file > > > * DATA STRUCTURES > > > D UPRMDS E DS EXTNAME(UPRM) INZ > > > D UCBSDS E DS EXTNAME(UCBS) INZ > > > D RTUPDWFDS E DS EXTNAME(RTUPDWF) INZ > > > > > >C/EXEC SQL > > >C+ Declare WRKCURS CURSOR FOR > > >C+ SELECT * FROM RTUPDWF > > >C/END-EXEC > > > > > > * OPEN THE CURSOR - ROUTE CHANGE WORK FILE > > > > > >C/EXEC SQL > > >C+ OPEN WRKCURS > > >C/END-EXEC > > > > > > * READ THROUGH THE FILE UNTIL EOF > > > > > >C DOU SQLCOD = 100 > > >C/EXEC SQL > > >C+ FETCH WRKCURS INTO :RTUPDWFDS > > >C/END-EXEC > > > > > > > > > C IF SQLCOD <> 100 > > > C IF SQLCOD = 00000 > > > > > > * GET ALL RECORDS IN UPRM FOR THE ROUTE SELECTED IN RTUPDWF > > > > > > C/EXEC SQL > > > C+ Declare PRMCURS CURSOR FOR > > > C+ SELECT * FROM UPRM > > > C+ WHERE UPRTE = :RTORT > > > C/END-EXEC > > > > > > * OPEN THE CURSOR - PREMISE FILE > > > > > > C/EXEC SQL > > > C+ OPEN PRMCURS > > > C/END-EXEC > > > > > > * READ THROUGH THE FILE UNTIL EOF > > > > > > C DOU SQLCOD = 100 > > > > > > C/EXEC SQL > > > C+ FETCH PRMCURS INTO :UPRMDS > > > C/END-EXEC > > > > > > C IF SQLCOD <> 100 > > > C IF SQLCOD = 00000 > > > C EXSR $UPDATEPRM > > > C ENDIF > > > C ENDIF > > > C ENDDO > > > > > > C EXSR $CLOSEPRM > > > > > > * GET ALL RECORDS IN UCBS FOR THE ROUTE SELECTED IN RTUPDWF > > > > > > C/EXEC SQL > > > C+ Declare CBSCURS CURSOR FOR > > > C+ SELECT * FROM UCBS > > > C+ WHERE USORT = :RTORT > > > C/END-EXEC > > > * OPEN THE CURSOR - ACCOUNT BILLED SERVICES FILE > > > > > > C/EXEC SQL > > > C+ OPEN CBSCURS > > > C/END-EXEC > > > > > > * READ THROUGH THE FILE UNTIL EOF > > > > > > C DOU SQLCOD = 100 > > > C/EXEC SQL > > > C+ FETCH CBSCURS INTO :UCBSDS > > > C/END-EXEC > > > > > > C IF SQLCOD <> 100 > > > C IF SQLCOD = 00000 > > > C EXSR $UPDATECBS > > > C ENDIF > > > C ENDIF > > > C ENDDO > > > > > > C EXSR $CLOSECBS > > > > > > C ENDIF > > > C ENDIF > > > ENDDO > > > C EXSR $CLOSEWRK > > > EVAL *INLR = *ON > > > *================================================================ > > > * PROGRAM SUBROUTINES > > > *---------------------------------------------------------------- > > > ***************************************************************** > > >C $UPDATEPRM BEGSR > > >C/EXEC SQL > > >C+ UPDATE UPRM SET > > >C+ UPRTE = :RTNRT > > >C/END-EXEC > > > > > > C ENDSR > > > ***************************************************************** > > > RTU > > > C $UPDATECBS BEGSR > > > C/EXEC SQL > > > C+ UPDATE UCBS SET > > > C+ USORT = :RTNRT > > > C/END-EXEC > > > > > > C ENDSR > > > ***************************************************************** > > > C $CLOSEWRK BEGSR > > > C/EXEC SQL > > > + CLOSE WRKCURS > > > C/END-EXEC > > > C ENDSR > > > ***************************************************************** > > > C $CLOSEPRM BEGSR > > > C/EXEC SQL > > > + CLOSE PRMCURS > > > C/END-EXEC > > > C ENDSR > > > ***************************************************************** > > > > > > ************************************* > > >C $CLOSECBS BEGSR > > >C/EXEC SQL > > > + CLOSE CBSCURS > > >C/END-EXEC > > >C ENDSR > > > *=================================== > > > > > > > > >Michael Smith > > > > > >+--- > > >| This is the RPG/400 Mailing List! > > >| To submit a new message, send your mail to RPG400-L@midrange.com. > > >| To subscribe to this list send email to > RPG400-L-SUB@midrange.com. > > >| To unsubscribe from this list send email to > > RPG400-L-UNSUB@midrange.com. > > >| Questions should be directed to the list owner/operator: > > david@midrange.com > > >+--- > > > > +--- > > | This is the RPG/400 Mailing List! > > | To submit a new message, send your mail to RPG400-L@midrange.com. > > | To subscribe to this list send email to RPG400-L-SUB@midrange.com. > > | To unsubscribe from this list send email to > > RPG400-L-UNSUB@midrange.com. > > | Questions should be directed to the list owner/operator: > > david@midrange.com > > +--- > > > +--- > | This is the RPG/400 Mailing List! > | To submit a new message, send your mail to RPG400-L@midrange.com. > | To subscribe to this list send email to RPG400-L-SUB@midrange.com. > | To unsubscribe from this list send email to > RPG400-L-UNSUB@midrange.com. > | Questions should be directed to the list owner/operator: > david@midrange.com > +--- > > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > +--- > | This is the RPG/400 Mailing List! > | To submit a new message, send your mail to RPG400-L@midrange.com. > | To subscribe to this list send email to RPG400-L-SUB@midrange.com. > | To unsubscribe from this list send email to > RPG400-L-UNSUB@midrange.com. > | Questions should be directed to the list owner/operator: > david@midrange.com > +--- > +--- | This is the RPG/400 Mailing List! | To submit a new message, send your mail to RPG400-L@midrange.com. | To subscribe to this list send email to RPG400-L-SUB@midrange.com. | To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com. | Questions should be directed to the list owner/operator: david@midrange.com +---
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
