|
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 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.
midrange.com
