Logo2
States & Cities
[Home] [Web & .json] [States & Cities]
StatesSorted

The SQLRPGLE for a sortable subfile
       //  _____________________________________________________________________
       //   ___             _    _     __ __             _    _
       //  | . > ___  ___ _| |_ | |_  |  \  \ ___  _ _ _| |_ <_>._ _
       //  | . \/ . \/ . \ | |  | . | |     |<_> || '_> | |  | || ' |
       //  |___/\___/\___/ |_|  |_|_| |_|_|_|<___||_|   |_|  |_||_|_|
       //
       //    Dec, 2019                           booth@martinvt.com
       //  _____________________________________________________________________
       //    Comments:
       //      - Web demo, States & Cities  
       //      - Sorted subfile using SQL and SQL's "global temporary table" 
       //        thus allowing sort, up or down, by the selected column
       //        whilst maintaining the sorting order of the other column(s).
       //  _____________________________________________________________________
       ctl-opt
       copyright('(C) Copyright Booth Martin 2019, All rights reserved.')
       option(*nodebugio) dftactgrp(*no) actgrp(*new);

       dcl-f WEBSTATESD workstn sfile(sfl1: sf1num);

       dcl-c cYLW x'32';
       dcl-c cWHTu x'26';
       dcl-c cBLUu x'3E';
       dcl-c cTrq x'30';

       dcl-c cURL '/home/Martin/hut/';
       dcl-s URL varchar(400);

       // Work fields
       dcl-s wState char(13);
       dcl-s wCity char(13);
       dcl-s wStateCount packed(5);

       //  Sorting by column headings:
       dcl-s SQLStmt char(1280);
       dcl-s wColumnSort packed(1) inz(2);
       dcl-s wCol1UpDn packed(1) inz(1);
       dcl-s wCol2UpDn packed(1) inz(1);
       dcl-s wReFillSFL ind inz(*on);

       dcl-ds *n PSDS;
         USERID char(10) pos(358);
       end-ds;

       //  _____________________________________________________________________
       //    SQL definitions (must be first lines of all sql lines in source.)
       exec sql set option
         DatFmt = *ISO,
         Commit = *chg,
         SrtSeq = *LangIDShr;   // allows sort & search with upper/lower
         exec sql declare C1 cursor for
           select *
           from json_table(Get_CLOB_from_file(:URL),
             '$.states[*]'
                 columns(
                   state varchar(13) path 'lax $.state',
                     nested '$.cities[*]'
                     columns(
                       "city" varchar(13) path 'lax $')
                    ));
       //  _____________________________________________________________________
       //                                        MAINLINE-BEGIN
       exsr GetHeading;
       exsr GetData;
       wRefillSFL = *on;
       exsr ColumnSort;
       //   Display screen.
       dow *inkc = *off;
         exsr ChangeColors;
         write S1CMD;
         exfmt FMT01;
         select;
         when *in03;              // Exit
         when PM_FLD = 'S1COL1' or PM_FLD = 'S1COL2';
           exsr ColumnSort;
         endsl;
       enddo;
       exec sql commit;
       *inlr = *on;
       //  _____________________________________________________________________
       //                                        MAINLINE-END
       //  _____________________________________________________________________
       //                                        Fill the Subfile
       begsr FillSFL;
         wReFillSFL = *off;
         // Clear subfile.
         *in50 = *on;
         write FMT01;
         *in50 = *off;
         SF1NUM = *zero;
         // Fill SFL.
         clear sqlcode;
         exec sql prepare Stmt1 from :SQLStmt;
         exec sql declare C2 cursor for Stmt1;
         exec sql open C2;
         dow sqlcode = 0;
           exec sql fetch C2 into :S1STATE, :S1CITY;
           if sqlcode = 0;
             SF1NUM += 1;
             write SFL1;
           endif;
         enddo;
         exec sql close C2;
         // If subfile is empty, make a line
         if SF1NUM = *zero;
           S1STATE = cYLW + 'No records =-';
           SF1NUM += 1;
           write SFL1;
         endif;
         NBRRECS = SF1NUM;
       endsr;
       //  _____________________________________________________________________
       //                                        Fill temporary table with data.
       begsr GetData;
         exec sql declare global temporary table TEMP1
           (STATES char(13) not null,
            CITIES char(13) not null);

         URL = cURL + 'states.json';
         exec sql select * into :wStateCount
           from json_table(Get_CLOB_from_file(:URL),
             'lax $'
              columns("statecount" dec(5, 0)) );
         exec sql open C1;
         clear sqlcode;
         dow sqlcode = 0;     // Table read loop
           exec sql fetch C1 into :wState, :wCity;
           if sqlcode = 0 and wState <> '' and wCity <> '';
             exec sql insert into TEMP1 (STATES, CITIES)
               values(:wState, :wCity);
           endif;
         enddo;
         exec sql close C1;
       endsr;
       //  _____________________________________________________________________
       //                                        Column sort
       begsr ColumnSort;
           if PM_FLD = 'S1COL1';
             if wColumnSort = 1;  //  flip order
               wCol1UpDn = wCol1UpDn * -1;
             else;
             wColumnSort = 1;
             endif;
             select;
               when wCol1UpDn = 1 and wCol2UpDn = -1;
                 SQLstmt = 'select * from TEMP1'
                         + ' order by STATES, CITIES desc';
               when wCol1UpDn = -1 and wCol2UpDn = 1;
                 SQLstmt = 'select * from TEMP1'
                         + ' order by STATES desc, CITIES';
               when wCol1UpDn = -1 and wCol2UpDn = -1;
                 SQLstmt = 'select * from TEMP1'
                         + ' order by STATES desc, CITIES desc';
               other;
                 SQLstmt = 'select * from TEMP1'
                         + ' order by STATES, CITIES';
             endsl;
             S1COL1 = cWHTu + 'States';
             S1COL2 = cBLUu + 'Cities';
           else;
             if wColumnSort = 2;  //  flip order
               wCol2UpDn = wCol2UpDn * -1;
             else;
             wColumnSort = 2;
             endif;
             select;
               when wCol1UpDn = 1 and wCol2UpDn = -1;
                 SQLstmt = 'select * from TEMP1'
                         + ' order by CITIES desc, STATES';
               when wCol1UpDn = -1 and wCol2UpDn = 1;
                 SQLstmt = 'select * from TEMP1'
                         + ' order by CITIES, STATES desc';
               when wCol1UpDn = -1 and wCol2UpDn = -1;
                 SQLstmt = 'select * from TEMP1'
                         + ' order by CITIES desc,STATES desc';
               other;
                 SQLstmt = 'select * from TEMP1'
                         + ' order by CITIES, STATES';
               endsl;
             S1COL1 = cBLUu + 'States';
             S1COL2 = cWHTu + 'Cities';
         endif;
         exsr FillSFL;
       endsr;
       //  _____________________________________________________________________
       //                                        Get Heading
       begsr GetHeading;
         HDG5X40 =
                   '  _      __    __                       '
                 + ' | | /| / /__ / /  -= States & Cities =-'
                 + ' | |/ |/ / -_) _ \ -=     Sortable    =-'
                 + ' |__/|__/\__/_.__/ -=     columns     =-';
         HDG7X23 =
                   '                       '
                 + '                       '
                 + '          ,,,          '
                 + '         (O-O)         '
                 + '  ----oo0-(_)-0oo----  '
                 + '                       '
                 + '                       ';
         exec SQL          // Get user's name to display.
           select CID.ODOBTX
             into :S1USERNAME
             from Table( QSYS2/USERS() ) AS CID
             where CID.ODOBNM = :USERID;
         evalr S1USERNAME = 'with' + cTrq + %trim(S1USERNAME);
       endsr;
       //  _____________________________________________________________________
       //                                        Change Heading Colors
       begsr ChangeColors;
         select;
         when *in61;
           *in61 = *off;
           *in62 = *on;
         when *in62;
           *in62 = *off;
           *in63 = *on;
         when *in63;
           *in63 = *off;
           *in64 = *on;
         when *in64;
           *in64 = *off;
           *in65 = *on;
         when *in65;
           *in65 = *off;
           *in66 = *on;
         when *in66;
           *in66 = *off;
           *in67 = *on;
         other;
           *in67 = *off;
           *in61 = *on;
         endsl;
       endsr; 




The DSPL file for a sortable subfile

      *  _______________________________________________________________________
      *     ___             _    _     __ __             _    _
      *    | . > ___  ___ _| |_ | |_  |  \  \ ___  _ _ _| |_ <_>._ _
      *    | . \/ . \/ . \ | |  | . | |     |<_> || '_> | |  | || ' |
      *    |___/\___/\___/ |_|  |_|_| |_|_|_|<___||_|   |_|  |_||_|_|
      *
      *   Dec, 2019                              booth@martinvt.com
      * ________________________________________________________________________
      *  Web Services, State & Cities -  sortable subfile
      *
      * ________________________________________________________________________
                                            DSPSIZ(*DS3)
                                            ERRSFL
                                            CA03(03 'Exit')
                                            CHGINPDFT
      * ------------------------------------------------------------------------
                R SFL1                      SFL
                  S1STATE       13      9 16
                  S1CITY        13        +6
      * ------------------------------------------------------------------------
                R FMT01                     SFLCTL(SFL1)
                                            SFLPAG(0011)
                                            SFLSIZ(&NBRRECS)
                                            OVERLAY
                                            MOUBTN(*ULP ENTER)
        50                                  SFLCLR
       N50                                  SFLDSP SFLDSPCTL SFLEND(*SCRBAR)
                                            RTNCSRLOC(&PM_RCD &PM_FLD)
                  PM_RCD        10   H
                  PM_FLD        10   H
                  SF1NUM         4S 0H
                  NBRRECS        5S 0P
                  RRNA           5S 0H
                                        1  2'System i'
                                        2  2'WEBSTATES'
                  HDG5X40      200   B  1 13CNTFLD(40) CHGINPDFT
                                            DSPATR(PR)
        67                                  COLOR(GRN)
        61                                  COLOR(PNK)
        62                                  COLOR(TRQ)
        63                                  COLOR(WHT)
        64                                  COLOR(BLU)
        65                                  COLOR(RED)
        66                                  COLOR(YLW)
                  HDG7X23      161   B  1 56CNTFLD(23) CHGINPDFT
                                            DSPATR(PR)
        61                                  COLOR(GRN)
        62                                  COLOR(PNK)
        63                                  COLOR(TRQ)
        64                                  COLOR(WHT)
        65                                  COLOR(BLU)
        66                                  COLOR(RED)
        67                                  COLOR(YLW)
                  S1USERNAME    50      6  3COLOR(YLW)
                  S1COL1        14   B  8 15DSPATR(PR)
                  S1COL2        14   B    +5DSPATR(PR)
      * ------------------------------------------------------------------------
                R S1CMD
                  PB1            2Y 0B 23 15PSHBTNFLD((*GUTTER 2))
                                            PSHBTNCHC(1 'Done' CA03)
                                            CHCAVAIL((*COLOR PNK))
                                       23 48'Click a Column Heading to sort.'
                                            COLOR(BLU)
                                       24 48'Click again to reverse the sort.'
                                            COLOR(BLU)  



[Home] [Code Samples] [SQL (simple)] [Subfiles] [Web & .json] [Contact Us] [Other]