Re: [Sort records in subfile] -- RPG400-L

Patrik:

I feel that the easiest and best way to allow the user flexiblity to
re-arrange a load subfile is to:

Position the cursor on the header(title line) of any column, retreive the
position(column) after enter is press, write the subfile to a keyed, tmp,
wrk, file.  [The file is laid out like the subfile with a extra key field.
The key is a large alpha field (the size of the largest field you will allow
to be sorted) ]  Fill the key with the data field from the column to be
sorted.  Populate the all fields in the file.  Clear and reload the subfile
from the tmp/wrk file.   This rountine usually runs pretty fast, but it will
depend on the subfile size and the machine.

I like using the cursor position and subfile record postion in many of my
inquiry programs.  The user, if using Client Access and setup correctly,
will be able to use their mouse.  When coded with this in mind, you can give
a "window/PC" feel better easily.   The user points to the title line,
double clicks, which emulates moving the cursor and pressing enter which
calls the sort routine.  I also allow them to double-click on a subfile
record which may call a detail inquiry screen, print routine or any other
needed rountine.

I can try to send you some of my code if you like, not promising it to be
brilliant but it works for me.  If interested, let me know.

Eurrat Saylor, Jr              endofthetrail@skyenet.net
9562 East  750 North
Walkerton (Koontz Lake), IN  46574
219-586-7970  tel
219-586-2677  fax

-----Original Message-----
From: Timura, Patrik (CAP, GCF) <Patrik.Timura@gecapital.com>
To: 'RPG400-L@midrange.com' <RPG400-L@midrange.com>
Date: Wednesday, June 28, 2000 12:03 PM
Subject: RE: [Sort records in subfile]


>AAARGH we don't have SQL dev kit :-(((( , but many thanks for your tip, it
>looks out to be very interesting.
>
>
>-----Original Message-----
>From: rjslaney@us.ibm.com [mailto:rjslaney@us.ibm.com]
>Sent: Wednesday, June 28, 2000 5:22 PM
>To: RPG400-L@midrange.com
>Subject: RE: [Sort records in subfile]
>
>
>
>
>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
>+---
>+---
>| 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
+---