RE: Embedded and dynamic SQLRPG

[João Pereira <JPereira@xxxxxxxxxxxxx>]

But, in this example you know what fields you wnat to fetch.
In my case I don't know how many, and which, fields the user select to
create a report or presented in an subfile.


Regards
João Pereira




-----Original Message-----
From: Jim Franz [mailto:jfranz@triad.rr.com]
Sent: Sexta-feira, 17 de Novembro de 2000 13:38
To: RPG400-L@midrange.com
Subject: Re: Embedded and dynamic SQLRPG


> I think something is missing:
> What is :save ???
>
Save is the data structure the record read is moved to. In this example,
that
happens to be only one field, but could be many.
ISAVE        DS
I                                        1   70WO#

the example of the STR is in the comments:
C* SELECT WO# FROM WO WHERE WOID# = 'CUS90' AND
C* WOPO# = 'XYZ1234'

So for each record read that meets the above selection, I can get the
work order number (WO#). I save the WO#'s from each record read,
and pass all of them in an array back to the calling pgm. To make it
clearer, below is a clip of the entire pgm. (again, I wrote this years
ago and might do some things diff today).

entire pgm

F*****************************************************************
F* NTOSQL3 - PROCESS SQL SEARCH AND RETURN ARRAY OF WORK ORDERS 'S
F*                       RETURN UP TO 100 WORK ORDERS FOR SELECTED PO#
F*
F*****************************************************************
F**
F** 02/15/98 JF WRITTEN
F*****************************************************************
E                    ARRAY   1   1 80               SQL STRING
E                    REF       100  7               ADDRESS REF#
ISAVE        DS
I                                        1   70WO#
C* MATCH NAME
C           *ENTRY    PLIST
C* INPUT
C                     PARM           ID      6        CUST ID
C                     PARM           PO#     7        PO#
C* OUTPUT
C                     PARM           REF              ADDR REF#'S
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*
C* PROCESS NO MORE THAN 10  RECORDS
C                     ADD  1         X
C           X         IFGT 100
C                     LEAVE
C                     ENDIF
C                     MOVE WO#       REF,X
C                     ENDDO
..... *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7
     C*
     C           $DONE     TAG
     C**                   MOVEAREF       REF#             MOVE REFS TO
     C           $ERR      TAG
     C                     MOVE *ON       *INLR
**    ARRAY
SELECT WO# FROM WO WHERE

+---
| 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
+---