Tweet
dynamic SQL processing
PHP Code:
*=============================================================
* Program - WKR44
* Purpose - example of free format and using dynamic SQL
*
*
* Written -
* Author -
* Program Description
*
*
*
* FromRoute Dec 3 Input
* ToRoute Dec 3 Input
*
* Indicator Usage
* None
*=============================================================
d WKR44 pr
d FromRoute 15 5
d ToRoute 15 5
d WKR44 pi
d FromRoute 15 5
d ToRoute 15 5
//Procedures
d openOrderList pr
d FetchNextOrder pr n
d closeOrderList pr
d count s 4 0
d date s 7 0 dim(200)
d ISODate s D
d outdate s 15 5
d outpronumber s 15 5
d pronumber s 7 0 dim(200)
d sd s 4 0
d sql s 32000a Varying
d sqldate s 7 0
d sqlerror s n
d sqlpronumber s 7 0
d todayCYMD s 7 0
d workiso s 8 0
d routedata e ds extname(wkartno) qualified
d $reroute pr extpgm('WKR28')
d outdt 15 5
d outPro# 15 5
/free
//--------------------------------------------------------
// M A I N L I N E
//--------------------------------------------------------
exsr Hskpg;
exsr $getpros;
exsr $deleteRoutes;
select;
when ToRoute <> *zeros;
exsr $rerouteOrds;
endsl;
*inlr = *on;
//--------------------------------------------------------
// $getpros - read the wkartno records save pro#'s
//--------------------------------------------------------
begsr $getpros;
sql = 'Select * from wkartno' +
' where wadst# = ' + %editc(%dec(FromRoute:3:0):'X') +
' and WADAT > ' + %editc(TodayCYMD:'X');
openOrderList();
Dow fetchNextOrder();
SD +=1;
pronumber(sd) = routedata.waproa;
date(sd) = routedata.wadat;
enddo;
closeOrderList();
endsr;
//--------------------------------------------------------
// $delteroutes - delete all the routes
//--------------------------------------------------------
begsr $deleteroutes;
for count = 1 to SD;
SQLdate = date(count);
SQLPronumber = pronumber(count);
/end-free
c/exec sql
c+ delete from WKARTNO
c+ where WADAT = : SQLdate and
c+ WAPROA = : SQLProNumber
c/end-exec
/free
endfor;
endsr;
//--------------------------------------------------------
// $rerouteOrds - call wkr28 to reroute the pro#'s
//--------------------------------------------------------
begsr $rerouteOrds;
for count = 1 to SD;
//convert the date from *CYMD to *ISO
SQLDate = date(count);
test(de) *cymd SQLDATE;
if not%error;
workIso =
%uns(%char(%Date(SQLDATE:*cymd):*iso0));
outdate = workIso;
endif;
outpronumber = pronumber(count);
$reroute(outdate: outpronumber);
endfor;
endsr;
//--------------------------------------------------------
// Hskpg - one time run subroutine
//--------------------------------------------------------
begsr Hskpg;
isodate = %date();
todayCYMD = %uns(%char(ISODATE:*cymd0));
endsr;
/end-free
c/exec sql
c+ set option commit=*none,
c+ datfmt=*iso
c/end-exec
*--------------------------------------------------------
* openOrderList - Open a cursor to read the Orders file
*--------------------------------------------------------
p openOrderList b
d openOrderList pi
c/exec sql
c+ declare wkartnoCursor cursor
c+ for wkStatement
c/end-exec
c/exec sql
c+ prepare wkStatement from :sql
c/end-exec
c/exec sql
c+ open wkartnoCursor
c/end-exec
p openOrderList e
*--------------------------------------------------------
* fetchNextOrder - read order one at a time
*--------------------------------------------------------
p fetchNextOrder b
d fetchNextOrder pi N
c/exec sql
c+ fetch next from wkartnoCursor
c+ into :routedata
c/end-exec
/free
if sqlstt < '02000';
return *on;
else;
return *off;
endif;
/end-free
p fetchNextOrder e
*--------------------------------------------------------
* closeOrderList - Close the OrderHdr cursor
*--------------------------------------------------------
p closeOrderList b
d closeOrderList pi
c/exec sql
c+ close wkartnoCursor
c/end-exec
p closeOrderList e
*--------------------------------------------------------
Comment