How to process files in weighted order with RPGLE/SQL

Here is an easy way to read from a file using RPGLE/SQL and weight the sequence (sort them) based on data within a specific field.

CSR EXSR01 BEGSR
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR
C+ SELECT DBFIELD1, DBFIELD2, DBFIELD3
C+ FROM DBFILE
C+ WHERE (DBFIELD1 = :RPGFIELD)
C+ ORDER BY DBFIELD1,
C+ CASE
C+ WHEN DBFILE.DBFIELD3 = ‘DEF’ THEN SMALLINT(10)
C+ WHEN DBFILE.DBFIELD3 = ‘ABC’ THEN SMALLINT(20)
C+ WHEN DBFILE.DBFIELD3 = ‘A01’ THEN SMALLINT(30)
C+ WHEN DBFILE.DBFIELD3 = ‘153’ THEN SMALLINT(40)
C+ ELSE SMALLINT(99)
C+ END DESC
C/END-EXEC
C/EXEC SQL
C+ OPEN C1
C/END-EXEC
C DOU SQLCODE <> *ZEROS
C/EXEC SQL
C+ FETCH C1 INTO :RPGFLDSTRUCT
C/END-EXEC
C IF SQLCODE = 100
/free
exec sql
CLOSE C1 ;
*INLR = *ON ;
/end-free
C LEAVESR . . . *exit loop
. . . DO SOMETHING
C ENDDO
C ENDSR

About Basil

Basil has over twenty years of programming experience from a wide variety of industries including warehouse distribution, insurance, state licensing for trucking industry, and discount retail. Programming language experience has predominantly been for IBM’s AS400 platform – CL, RPG, COBOL, Queries, Managed Queries, APF, SQL, Synon, Visual Basic and C++. He obtained his masters from the University of Mary-Hardin Baylor in 2007.
This entry was posted in ISeries and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *