|
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)
|
|
|