|
the following builds a dynamic fetch in field "STR". This was written years ago and is still running. I included only part of the build of the string. hth jim C* INITIALIZE C MOVE '''' QUOTE 1 C MOVE '%' PERCNT 1 C MOVE 'WOID# =' @WOID# 7 WO ID# C MOVE 'WOPO# =' @PO# 7 PO# C MOVE 'LIKE' @LIKE 4 C MOVE 'AND' @AND 3 C MOVE *ZEROS X 50 # OF RECS C MOVE *BLANKS REF ADDRESS REF# C* BUILD COMMAND STRING C MOVEAARRAY STR 256 P CMD STRING C MOVE *ZEROS #FLDS 30 #FIELDS USED C* WO ID# C* SELECT WO# FROM WO WHERE WOID# = 'XXXXXX' C ID IFNE *BLANKS C ADD 1 #FLDS C CAT @WOID#:1 STR C CAT QUOTE:1 STR C CAT ID:0 STR C CAT QUOTE:0 STR C ENDIF C* PO NUMBER C* SELECT WO# FROM WO WHERE WOID# = 'CUS90' AND C* WOPO# = 'XYZ1234' C PO# IFNE *BLANKS C ADD 1 #FLDS C #FLDS IFGT 1 C CAT @AND:1 STR AND C ENDIF C CAT @PO#:1 STR AND WOPO# C CAT QUOTE:1 STR AND WOPO# ' C CAT PO#:0 STR AND WOPO# 'X C CAT QUOTE:0 STR C ENDIF C* C/EXEC SQL WHENEVER SQLERROR GOTO $ERR C/END-EXEC C* C/EXEC SQL C+ PREPARE S1 FROM:STR C/END-EXEC C* C/EXEC SQL C+ DECLARE C1 CURSOR FOR S1 C/END-EXEC C* C/EXEC SQL OPEN C1 C/END-EXEC C* C/EXEC SQL WHENEVER NOT FOUND GOTO $DONE C/END-EXEC C* C SQLCOD DOUNE0 C/EXEC SQL C+ FETCH C1 INTO :SAVE C/END-EXEC C........ ----- Original Message ----- From: "João Pereira" <JPereira@Auto-Sueco.PT> To: <RPG400-L@midrange.com> Sent: Friday, November 17, 2000 5:06 AM Subject: RE: Embedded and dynamic SQLRPG > > And the fetch? > > How can we create an dynamic fetch ? > e.g. > like building an expression: > eval dfetch=':var1, :var2, ...' > and then > > C/EXEC SQL > C+ FETCH NEXT FROM DYNFIL USING DESCRIPTOR :dfetch > > > Regards > Joao > > > > -----Original Message----- > From: groyle@cott.com [mailto:groyle@cott.com] > Sent: Terça-feira, 14 de Novembro de 2000 20:33 > To: RPG400-L@midrange.com > Subject: re: Embedded and dynamic SQLRPG > > > It looks to me like you're doing the declare before the prepare. > > Here's an example that we use (in RPG IV) that does exactly what you want. > We > let the user specify a Sort Order and create the ORDER BY accordingly. > > D Dyn_Sel S 796A > D Dyn_Sela S 796A > > C Eval Dyn_Sela = 'SELECT LHID, LHYEAR, ' + > C 'LHPERD, LHJNEN, LHJNLN, ' + > C 'LHIAN, LHDRAM, LHCRAM, ' + > C '(LHDRAM - LHCRAM), LHLDES, ' + > C Sort1+', ' + > C Sort2+', ' + > C Sort3 + > C ' FROM GLH ' + > C 'LEFT OUTER JOIN GLA ' + > C 'ON (LHJNEN = LAJNEN AND LHJNLN = LAJNLN) '+ > C 'WHERE (LHLDGR ='+''''+LEDGER + > C ''''+' AND LHBOOK = '+'''' + > C BOOK+''''+' AND ' + > C '(LHYEAR = '+%CHAR(FYEAR)+') AND ' + > C '(LHPERD = '+%CHAR(FPERD)+') AND ' + > C 'LHJNEN BETWEEN '+''''+JRNENFM+'''' + > C ' AND'+''''+JRNENTO+''''+' AND ' + > C 'LHID = '+LH+' AND ' + > > C 'LHIAN = '+ %CHAR(IAN)+' AND ' + > > C 'LHLSTS = 3) ' > > ---- > > C If Selection = *ON AND Sort = *OFF > > C Eval Dyn_Sel = Dyn_Sela + > > C 'ORDER BY LHPERD, LHYEAR, LHLDES ' + > > C 'FOR READ ONLY' > > C Else > > C* > > C If Selection = *ON AND Sort = *ON > > C Eval Dyn_Sel = Dyn_Sela + > > C 'ORDER BY LHPERD, LHYEAR, LHLDES, ' + > > C Sort1+', '+Sort2+', '+Sort3 + > > C 'FOR READ ONLY' > > C Else > > C If Sort = *ON AND Selection = *OFF > > C Eval Dyn_Sel = Dyn_Sela + > > C 'GROUP BY LHPERD, LHYEAR, ' + > > C Sort1a+', '+Sort2a+', ' + > > C > Else > C Eval Dyn_Sel = Dyn_Sela > + > C 'ORDER BY LHPERD, LHYEAR ' > + > C 'FOR READ > ONLY' > C > EndIf > C > EndIf > C > EndIf > > C/EXEC SQL > C+ PREPARE Dyn_Select FROM :Dyn_Sel > C/END-EXEC > C* > C/EXEC SQL DECLARE TR_DETAIL CURSOR FOR Dyn_Select > C/END-EXEC > C* > > These are just code snippets taken (out of context) from a working program > but they wil;l give you the idea. > > Gord Royle > > Hope it helps > ---------- Original Text ---------- > > From: Jo~o Pereira <JPereira@Auto-Sueco.PT>, on 11/14/00 2:27 PM: > > > > Please look at the following RPG. > > > > 0004.00 C *ENTRY PLIST > > 0004.01 C PARM PRI 6 > > > > > > 0005.00 C/EXEC SQL > > 0005.01 C+ DECLARE DYNFIL SCROLL CURSOR FOR DYNSTM > > 0005.02 C/END-EXEC > > > > 0011.00 C MOVEL'SELECT' WWHERE256 P > > 0012.00 C CAT '* FROM':1 WWHERE > > 0013.00 C CAT 'MCLI':1 WWHERE > > 0014.00 C CAT 'WHERE':1 WWHERE > > 0014.01 C CAT '(':1 WWHERE > > 0015.00 C CAT 'CFNUM':0 WWHERE > > 0016.00 C CAT '>=':0 WWHERE > > 0017.00 C CAT PRI:0 WWHERE > > 0022.00 C CAT ')':0 WWHERE > > > > 0030.00 C/EXEC SQL > > 0031.00 C+ PREPARE DYNSTM FROM :WWHERE > > 0032.00 C/END-EXEC > > 0033.00 * > > 0034.00 C/EXEC SQL > > 0035.00 C+ OPEN DYNFIL USING :WWHERE > > 0036.00 C/END-EXEC > > 0037.00 * > > 0038.00 * LIST FILE > > 0039.00 B1 C SQLCOD DOWNE100 > > 0040.00 * read forward > > 0041.00 * > > 0042.00 C/EXEC SQL > > 0043.00 C+ FETCH NEXT FROM DYNFIL > > 0045.00 C/END-EXEC > > 0046.00 * > > 0047.00 B2 C SQLCOD IFEQ 100 > > > > 0048.00 C LEAVE > > 0049.00 E2 C END > > 0050.00 * > > 0055.00 C EXCPTDET > > 0056.00 * > > 0057.00 E1 C ENDDO > > > > 0059.00 C/EXEC SQL > > 0060.00 C+ CLOSE DYNFIL > > 0061.00 C/END-EXEC > > 0062.00 * > > 0063.00 OQSYSPRT E 1 DET > > 0064.00 O CFNUM Z 7 > > 0064.01 O ' ' > > 0065.00 O CFNOM > > > > > > > > As this is a dynamic SQL, how can I retrieve the fields of my file (MCLI) > > in the fetch? > > > > I'd like to give the users the possibility of selecting the fields they > > want. > > > > > > Regards > > Joao Pereira > > > > > > > > > > > > > +--- > | 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 > +--- > > > > > > _______________________________________________________________ > > Cott - The Leader in Premium Retailer Brand Beverage Innovation. > _______________________________________________________________ > +--- > | 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 +---
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
