|
Chris, Here is an example for you. It is writtin in RPG III with a Prepared Statement. Thanks George >>> ChrisBaker@xxxxxxxxxxxxxxx 4/2/2004 6:15:18 AM >>> Can anybody help I have a display file with up to 12 selection fields, all are optional, and would like to build a subfile based on the the results of an SQL selection. I believe that it is possible with a dynamic SQL selection but have been unable to get past the pre-compiler error message SQL 0312. could anybody please provide me with an example in RPG (Not ILE). Any help would be greatly appreciated. _________________________________________________ Regards Chris Baker Telephone : 02476 852625 email : <mailto:chrisbaker@xxxxxxxxxxxx> chrisbaker@xxxxxxxxxxxxxxx ********************************************************************************* This email is intended solely for the use of the individual to whom it is addressed and may contain confidential and/or privileged material. Any views or opinions presented are solely those of the author and do not necessarily represent those of AGCO. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of this email is strictly prohibited. Neither AGCO nor the sender accepts any responsibility for viruses and it is your responsibility to scan and virus check the e-mail and its attachment(s) (if any). ********************************************************************************* _______________________________________________ This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.
midrange.com
H 1
*
E FLDS 1 1 80 Select fields
E FILS 1 1 80 Files
E JOIN 1 1 80 Join
E ORDR 1 1 80 Order by
* SQL statement
I@STMNT IDS 1024
I DS
I 1 80 @FLDS
I DS
I 1 80 @FILS
I DS
I 1 80 @JOIN
I DS
I 1 80 @ORDR
* Used to define SQL fields
IDATA IDS
I 1 3 %%FLD
ITOTALS IDS 11 %TOTAL
*
I 'For Fetch Only' C FETON
*
/SPACE 3
***********************************************************
* *$MAIN - Mainline
***********************************************************
* $MAIN
*
C EXSR $INZSR Initialize
C EXSR $FETCH Read record
C @EOF IFEQ *ON
*
C ELSE
C @EOF DOWEQ*OFF
C EXSR $FETCH Read record
C ENDDO @EOF
C ENDIF
*
/SPACE 3
***********************************************************
* *$INZSR - Initialize program
***********************************************************
*
C $INZSR BEGSR
*
C/EXEC SQL
C+ Declare C_%%FORMAT Cursor For S_%%FORMAT
C/END-EXEC
C EXSR $TODAY Date *USA
*
C EXSR $BLDSL Build Select
C EXSR $BLDWH Build Where
C EXSR $BLDOB Build Order By
C EXSR $PREPR Prepare Stmnt
C EXSR $OPENC Open Cursor
*
C ENDSR
/SPACE 3
***********************************************************
* *$ENDPG - End program
***********************************************************
*
C $ENDPG BEGSR
*
C MOVE *ON *INLR
C EXSR $CLOSC Close SQL
C RETRN
*
C ENDSR
/SPACE 3
***********************************************************
* *$TODAY - Convert UDATE into MM-DD-CCYY format
* Returns @@TDAY with today's date
***********************************************************
*
C $TODAY BEGSR
*
C MOVE *MONTH @@MNTH 2
C MOVE *DAY @@DAY 2
C MOVE *YEAR @@YEAR 4
C @@MNTH CAT '/' @@TDAY 10 P
C CAT @@DAY:0 @@TDAY
C CAT '/':0 @@TDAY
C CAT @@YEAR:0 @@TDAY
*
C ENDSR
/SPACE 3
***********************************************************
* *$BLDSL - Build select clause
***********************************************************
C $BLDSL BEGSR
*
C MOVEL'SELECT' @STMNT P SELECT token
C MOVEAFLDS,1 @FLDS P Fields
C CAT @FLDS:1 @STMNT
C CAT 'FROM':1 @STMNT FROM token
C MOVEAFILS,1 @FILS P Files
C CAT @FILS:1 @STMNT
*
C ENDSR
/SPACE 3
***********************************************************
* *$BLDWH - Build where clause
***********************************************************
C $BLDWH BEGSR
*
C @STMNT CAT 'WHERE':1 @STMNT WHERE token
C MOVEAJOIN,1 @JOIN P Joins
C CAT @JOIN:1 @STMNT Statement
*
C ENDSR
/SPACE 3
***********************************************************
* *$BLDOB - Build order by clause
***********************************************************
C $BLDOB BEGSR
*
C CAT 'ORDER':1 @STMNT Order by token
C CAT 'BY':1 @STMNT
C MOVEAORDR,1 @ORDR P Order fields
C CAT @ORDR:1 @STMNT Statement
*
C ENDSR
/SPACE 3
********************************************************************
* $PREPR - Prepare an SQL statement for execution *
********************************************************************
*
C $PREPR BEGSR
*
C CAT FETON:1 @STMNT Fetch only
*
C/EXEC SQL
C+ Prepare S_%%FORMAT From :@STMNT
C/END-EXEC
*
C SQLCOD CASLT*ZERO *PSSR SQL error
C ENDCS
*
C ENDSR
/SPACE 3
********************************************************************
* $OPENC - Open SQL cursor for statement *
********************************************************************
*
C $OPENC BEGSR
C/EXEC SQL
C+ Open C_%%FORMAT
C/END-EXEC
*
C SQLCOD CASLT*ZERO *PSSR SQL error
C ENDCS
*
C ENDSR
/SPACE 3
********************************************************************
* $CLOSC - Close SQL cursor for statement *
********************************************************************
*
C $CLOSC BEGSR
C/EXEC SQL
C+ Close C_%%FORMAT
C/END-EXEC
*
C SQLCOD CASLT*ZERO *PSSR SQL error
C ENDCS
*
C ENDSR
/SPACE 3
***********************************************************
* *$FETCH - Read a row from SQL table
***********************************************************
*
C $FETCH BEGSR
C/EXEC SQL
C+ Fetch C_%%FORMAT Into :DATA
C/END-EXEC
*
C SQLCOD IFEQ 100 End of file
C MOVE *ON @EOF 1
C ELSE
C MOVE *OFF @EOF
C ENDIF
C SQLCOD CASLT*ZERO *PSSR SQL error
C ENDCS
*
C ENDSR
/COPY C_PSSR *PSSR Std. Error Handler SOCLON
** Fields to select
** Files to select from
** File joins part of where
** Order by
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.
