December 1

15 comments

Example of an IBM i RPG Single Page Subfile

By NickLitten

December 1, 2020

subfile, example, RPG, RPGLE, sample, SFL, SFLPAG, subfile

What are single page subfiles?

A single page subfile is a SCREEN OF DISPLAYED DATA, loaded one page at a time. The displayed data is equal to the maximum number of records that can be displayed at a time.

In other words, in a single page subfile, all loaded records are displayed at a time. We delete all previously loaded records from the subfile whenever we need to load the next page of the subfile.

A single page subfile is characterized by the subfile size equalling the page size. Also, in a single page subfile, both ROLLUP and ROLLDOWN keywords must be defined in the DDS of the subfile control format.

This is because the PAGEUP/PAGEDOWN activities are handled by the program

Example Single Page Subfile in SQL RPGLE

The Table (or Physical File) is SAMPLETBL

RPG Code Snippet Single Page Subfile
  * Description... Sample Customer Database
  * File Name..... SAMPLETBL.PF
  * Author........ Nick Litten (https://www.nicklitten.com)
 A                                      UNIQUE
 A          R CUSRCDFMT
 A            CODE          10          COLHDG('Customer' 'Code')
 A            FORENAME      20          COLHDG('Customer' 'First Name')
 A            SURNAME       20          COLHDG('Customer' 'SurName')
 A            BIRTHDAY       8  0       COLHDG('Customer' 'Birthday')
 A            PHONE         15  0       COLHDG('Customer' 'Phone')
 A            EMAIL        100          COLHDG('Customer' 'Email')
 A            STREET1       50          COLHDG('Customer' 'Address 1')
 A            STREET2       50          COLHDG('Customer' 'Address 2')
 A            CITY          50          COLHDG('Customer' 'City')
 A            STATECODE     50          COLHDG('Customer' 'State')
 A            ZIP           20          COLHDG('Customer' 'Zip')
 A            COUNTRY       50          COLHDG('Customer' 'Country')
 A          K CODE 

Display File is SAMPLEPNL

This is in IBM i DDS DSPF Format:

  * Description... Sample Customer Database
  * File Name..... SAMPLEPNL.DSPF
  * Author........ Nick Litten (https://www.nicklitten.com)
 A                                      DSPSIZ(27 132 *DS4)
 A                                      REF(SAMPLETBL)
 A                                      PRINT
 A                                      INDARA
 A                                      ERRSFL
 A                                      HELP(01)
 A                                      CA03(03)
 A                                      CA05(05)
 A                                      CA12(12)
 A                                      PAGEUP(25)
 A                                      PAGEDOWN(26)
 A          R SFL01                     SFL
 A            SFLRRN         5S 0H
 A            EMAIL     R        H
 A            STREET1   R        H
 A            STREET2   R        H
 A            CITY      R        H
 A            STATECODE R        H
 A            ZIP       R        H
 A            COUNTRY   R        H
 A            CODE      R        O  5  5
 A            FORENAME  R        O  5 17
 A            SURNAME   R        O  5 39
 A            BIRTHDAY  R        O  5 60
 A                                      EDTWRD('  /  /    ')
 A            PHONE     R        O  5 71
 A            CALCADD       40A  O  5 87TEXT('Combined Address')
 A          R CTL01                     SFLCTL(SFL01)
 A                                      SFLSIZ(0020)
 A                                      SFLPAG(0020)
 A                                      OVERLAY
 A  30                                  SFLDSP
 A  31                                  SFLDSPCTL
 A  32                                  SFLCLR
 A  35                                  SFLEND(*SCRBAR *MORE)
 A                                  1  3'Sample Subfile Program in IBM i RP-
 A                                      GLE'
 A                                      COLOR(WHT)
 A                                  1 98'System'
 A                                      COLOR(BLU)
 A                                  1105SYSNAME
 A                                  1118'Date'
 A                                      COLOR(BLU)
 A                                  1123DATE
 A                                      EDTCDE(Y)
 A                                  2100'User'
 A                                      COLOR(BLU)
 A                                  2105USER
 A                                  2118'Time'
 A                                      COLOR(BLU)
 A                                  2123TIME
 A                                  3  5'Customer ID'
 A                                      COLOR(BLU)
 A                                  3 17'Customer'
 A                                      COLOR(BLU)
 A                                  3 39'Customer'
 A                                      COLOR(BLU)
 A                                  3 61'Customer'
 A                                      COLOR(BLU)
 A                                  3 71'Customer'
 A                                      COLOR(BLU)
 A            POSITION  R        O  4  5REFFLD(CODE SAMPLETBL)
 A                                      COLOR(BLU)
 A  31                                  DSPATR(UL)
 A  31                                  DSPATR(PC)
 A N30                                  DSPATR(ND)
 A                                  4 17'Forename'
 A                                      COLOR(BLU)
 A                                  4 39'Surname'
 A                                      COLOR(BLU)
 A                                  4 61'Birthday'
 A                                      COLOR(BLU)
 A                                  4 71'Contact Num'
 A                                      COLOR(BLU)
 A                                  4 87'Address'
 A                                      COLOR(BLU)
 A          R CMD01
 A                                      OVERLAY
 A            MOUSECLICK     2Y 0B 25  3PSHBTNFLD((*NUMROW 1) (*GUTTER 1))
 A                                      PSHBTNCHC(1 '>Enter')
 A                                      PSHBTNCHC(3 'e>Xit')
 A                                      PSHBTNCHC(5 '>Refresh')
 A            ERRORMSG     120A  O 26  2COLOR(RED) 

SQLRPGLE for Single Page Subfile Processing

Now the program is RPGLE but using SQL to load the tables:

**FREE

// Description... Sample Customer Database Subfile Program
// File Name..... SAMPLEPGM.SQLRPGLE
// Author........ Nick Litten (https://www.nicklitten.com)
// Date.......... Nov 18th 2016
//
// Compile Instructions:
//
// CRTSQLRPGI SRCFILE(yourlib/QRPGLESRC)
//            SRCMBR(SAMPLEPGM)
//            OBJTYPE(*MODULE)
//
// CRTPGM PGM(yourlib/SAMPLEPGM)
//

ctl-opt
 debug
 option(*nodebugio:*srcstmt)
 datfmt(*iso-) timfmt(*iso.)
 indent('| ') truncnbr(*yes) expropts(*resdecpos)
 copyright('| SAMPLEPNL V000 Example Single Page Subfile');

dcl-f SAMPLEPNL workstn sfile(SFL01:rrn)  indDs(dspf);

dcl-s p_Indicators pointer inz(%addr(*in));
dcl-ds dspf qualified based(p_Indicators);
  help ind pos(01);
  exit ind pos(03);
  refresh ind pos(05);
  previous ind pos(12);
  pageup ind pos(25);
  pagedown ind pos(26);
  sflclr ind pos(32);
  sfldsp ind pos(30);
  sflctl ind pos(31);
  sflEnd   ind pos(35);
end-ds;

dcl-ds dsp_fields extname('SAMPLETBL') end-ds;

dcl-s rrn like(sflrrn);
dcl-s previousPosition like(position);
dcl-s pagesize zoned(2) inz(20);

exec sql
  set option commit = *none,
             closqlcsr = *endmod;

// initially let's force a refresh to load first page
dspf.refresh = *on;

dou dspf.exit or dspf.previous;

  if dspf.refresh;
    clear position;
    clearSubfile();
    setPosition();
    loadSubfile();
  elseif dspf.pageUp;
    setPosition();
    pageUp();
  elseif dspf.pagedown;
    pageDown();
  endif;

  showSubfile();

enddo;

*inlr = *on;
return;





// Clear Subfile procedure...
dcl-proc clearSubfile;

  dspf.sflEnd = *off;
  dspf.sflclr = *on;
  write ctl01;
  dspf.sflclr = *off;

end-proc;





// Build Subfile procedure...
dcl-proc loadSubfile;

  dspf.sflEnd = *off;
  rrn = 0;

  exec sql
    fetch next from mycursor
      into :dsp_fields;

  previousPosition = code;

  dow sqlcode >= 0 and sqlcode < 100;
    rrn += 1;
    write SFL01;
    if rrn = pagesize;
      leave;
    endif;

    exec sql
      fetch next from mycursor
        into :dsp_fields;
  enddo;

  // if we didnt load a full page then set END OF SUBFILE
  if rrn < pagesize;
    dspf.sflEnd = *on;
  endif;

end-proc;





// Display Subfile procedure...
dcl-proc showSubfile;

  if rrn > 0;
    dspf.sfldsp = *on;
  else;
    dspf.sfldsp = *off;
    errormsg = 'No data loaded from SAMPLETBL!';
  endif;

  dspf.sflctl = *on;

  write cmd01;
  exfmt ctl01;

  clear ERRORMSG;

end-proc;





// Declare cursor procedure...
dcl-proc setPosition;

  exec sql
    close mycursor;

  exec sql
    declare mycursor scroll cursor for
      select code,
             forename,
             surname,
             birthday,
             phone,
             email,
             street1,
             street2,
             city,
             statecode,
             zip,
             country
        from sampletbl
        where code >= :position
        order by code
        for read only;

  exec sql
    open mycursor;

end-proc;





// Page up procedure...
dcl-proc pageUp;

  // set start cursor RRN at first row of previous page
  if rrn = pagesize;
    position = previousPosition;
  else;
    clear position;
  endif;

  // Build the subfile starting at *position*
  clearSubfile();
  loadSubfile();

  // If the cursor already in the top the list
  if rrn <= pagesize;
    errormsg = 'Start of customer list';
  endif;

end-proc;





// Page down procedure...
dcl-proc pageDown;

  // Build the subfile starting at *position*
  clearSubfile();
  loadSubfile();

  // If more to read then show sflend
  if rrn < pagesize;
    errormsg = 'You have reached the bottom of the customer list.';
  endif;

end-proc;
// -- end of code -- 

Want to learn how to write RPGLE programs?

  • Hi Nick,
    Does your IBM i RPG Subfile program run in OS version V5R4 , because I’ve got several highlighted lines in the source code ?
    Thanks in advance.

  • and/or a dynamic-ORDER BY clause.
    Example is when you have multiple of input fields as key-fields when starting to sort, and when these input fields (on the CTL-record-format) have changed it’s values.
    Maybe an exec-sql PREPARE is necessary?
    Please advise. Thank you.

  • Hello Nick! I stumbled upon your free-format RPG and embedded SQL from your website, about single-page subfiles.
    I like your coding style! Thank you! …
    By the way, do you have code-snippets or logic where the program/dspf requires a “Position To”?
    Thanks again!

    • I used to use the DSPF AID byte all the time – but for when creating simple examples I find the mapping the *indicator array a clearer way to explain the function keys. It could, of course, be argued both ways. Both just as good as each other. 🙂

    • Again, agree totally. But.. in this code example I was writing it and showing it to a .net programmer that preferred if/elseif/elseif layout… so his preference just kind of stuck with me. 😉

      When I’m writing my own code I tend to use *SELECT* groups in both RPG and CLLE because I like the indentation layout of them… seems cleaner to my eyes.

  • Argh! I didn’t have **FREE starting in position 1. Now it compiles fine. Sorry for the dumb comments above. I can’t seem to delete my comments.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

    >