RE: [Sort records in subfile]

[rjslaney@xxxxxxxxxx]

Patrik, in order to do as Brian suggests you would need the SQL Development
Kit (5769-ST1) installed on your system. Basically, SQL would take the
place of a loop that reads your data records.  As part of the SELECT
statement, you would code an ORDER BY clause which would place all the
records you want in a "result set" in the order you desire.

Then you would load the subfile as you would normally. To get the
individual records (or rows) from the result set you would use an SQL FETCH
statement followed by an RPG WRITE to the subfile.

Here is a sample program (originally form Brian Parkins) that is used in
the IBM Learning Services cours that teaches how to use Embedded SQL.  The
fact that you can create a sorted set of records with a single line of code
is only one of the many powers of SQL.

 ** Compile options:
 **       DATFMT(*ISO) DATSEP(-) COMMIT(*NONE) CLOSQLCSR(*ENDMOD)

FEmbed04D  CF   E                             Workstn Sfile(Data:Rrn)


D Indicators                DS
Based(IndPtr)
D   Exit                                                                1N
Overlay(Indicators:03)
D   SflEnd                                                          1N
Overlay(Indicators:40)
D   SflDspCtl                                                     1N
Overlay(Indicators:85)
D   SflDsp                                                          1N
Overlay(Indicators:95)

D Rrn                            S                                   5P 0
Inz(1)
D IndPtr                        S                                     *

D ErrorMsg                 S                                50A   Inz('An
error has occurred +
D
- SQLCODE = ') Varying

C                                            Eval              IndPtr =
%Addr(*In)
C                                            Write             FncKey

C                                            Eval              SflEnd = *ON

 ** Monitor for Errors
C/Exec Sql
C+ WHENEVER SQLERROR GOTO ERRORLBL
C/End-Exec
 ** Declare Cursor
C/Exec Sql
C+ DECLARE APINVCSR CURSOR FOR
C+   SELECT PONBR, VNDNBR, APINVNBR, POTOTAMT, APNETPAID,
C+       APDUEDATE, APDATEPAID
C+     FROM APINV_PF
C+     ORDER BY PONBR
C/End-Exec
  ** Open Cursor
C/Exec Sql
C+ OPEN APINVCSR
C/End-Exec
 ** Fetch first Row
C/Exec Sql
C+ FETCH NEXT FROM APINVCSR
C+   INTO :PONBR, :VNDNBR, :APINVNBR, :POTOTAMT, :APNETPAID,
C+     :APDUEDATE, :APDATEPAID
C/End-Exec
  ** Load entire Subfile
C                                          Dow            SqlCod <> 100

C                                          Write           Data

  ** Fetch next Row
C/Exec Sql
C+ FETCH NEXT FROM APINVCSR
C+   INTO :PONBR, :VNDNBR, :APINVNBR, :POTOTAMT, :APNETPAID,
C+     :APDUEDATE, :APDATEPAID
C/End-Exec
 **
C                                        Eval              Rrn = Rrn + 1

C                                        Enddo

 ** Display subfile if file not empty
C                                       Select


C                                      When             Rrn > 1

C                                      Eval                SflDspCtl = *ON

C                                      Eval                SflDsp    = *ON


C                                      Dow               NOT Exit

C                                      Exfmt             Control

C                                      Enddo


C                                      When            Rrn <= 1

C                                      Eval               SflDspCtl = *ON

C                                      Eval               SflDsp    = *OFF


C                                     Write              Msg

C                                     Exfmt              Control


C                                    Endsl

 ** Close Cursor
C/Exec Sql
C+ CLOSE APINVCSR
C/End-Exec

C                                   Eval                 *InLR = *ON

C                                   Return


C             ErrorLbl      Tag

C                                   Eval                 ErrorMsg =
ErrorMsg +
C
                                                                                
   

TrimL(%EditC(SqlCod:'1'))
C             ErrorMsg    Dsply     '*REQUESTER'


 ** Close Cursor
C/Exec Sql
C+ CLOSE APINVCSR
C/End-Exec

C                                   Eval                  *InLR = *ON

C                                   Return


Bob

Bob Slaney/Atlanta/IBM @ IBMUS (Lotus Notes)
VM id: WTSCPOK.RJSLANEY@VM
Internet id: rjslaney@us.ibm.com
Tieline 596-3022 Direct (770)835-3022
Fax tie 596-3912 Direct (770)835-3912

IBM Learning Services
AS/400 Course Materials Development
IBM Corp.
3100 Windy Hill Road
Atlanta, GA 30339
Internal Zip WE3A1


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