× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  • Subject: RE: [Sort records in subfile]
  • From: Peter Connell <peterc@xxxxxxxxxxxxx>
  • Date: Thu, 29 Jun 2000 09:15:44 +1200

Of course, you don't need SQL installed if you are just embedding SQL in RPG
since this is supported as a native feature in OS/400.
Peter

-----Original Message-----
From: Timura, Patrik (CAP, GCF) [mailto:Patrik.Timura@gecapital.com]
Sent: Thursday, June 29, 2000 4:29 AM
To: 'RPG400-L@midrange.com'
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
+---

**********************************************************************************************************
Privileged / Confidential Information may be contained in this message. If you 
are not the addressee indicated in this message (or responsible for delivery of 
the message to such person), you may not copy or deliver this message to 
anyone. In such case, you should destroy this message, and please notify us 
immediately. 

Please advise immediately if you or your employer does not consent to Internet 
e-mail for messages of this kind. Opinions and other information expressed in 
this message are not given or endorsed by my firm or employer unless otherwise 
indicated by an authorised representative independent of this message.
+---
| 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 thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.