On 18-May-2011 20:17 , Evan Harris wrote:
I have a project where I want to process a list of entries obtained
from a detail table against a control table to determine whether
certain processing should take place. Some kind of processing will
take place on all entries - the control file will determine which
entries will be treated differently.
For example, if processing a list of users e.g. BOB, CHARLIE, DAVID,
JACK, JOHN, MICHAEL and RICHARD
and a control file like BOB, DAVE, J*...
How would I match the JACK and JOHN detail entries to the J* entry
in the control table to retrieve processing control data ?
I've thought of a couple of possibilities but they seem to be ugly.
One was to first build an expanded the list by using the generic
entries to query the control table and then matching detail entries
against the expanded list.
Any other ideas out there ?
Using SQL to perform the searches, with a generated statement.? The
following uses REXX for convenience, but it could be done purely SQL or
another language:
addpfm qrexsrc sltusers srctype(rexx) text('select using ctl file')
/* copy <code> data into src mbr added above */
strrexprc sltusers parm('setup')
upddta qtemp/userslt /* or SQL DML; modify slt in control file */
strrexprc sltusers /* results should reflect changes to slt */
<code>
parse upper arg inpval
address "*EXECSQL"
execsql "set option commit=*NONE"
if inpval="SETUP" then call setup
execsql "declare BldSlt cursor for ",
"select dec(locate('*',s),2) ",
" , '''' concat replace(s,'*','%') concat '''' ",
"from qtemp/userslt"
/* say "declare BldSlt " sqlcode ":" sqlstate */
execsql "open BldSlt" ; if rc<>0 then exit rc
/* say "open BldSlt " sqlcode ":" sqlstate */
SltCnt=0
inTest='' ; lkTest=''
do while sqlcode<>100
execsql "fetch BldSlt into :astpos, :tstval"
/* say "fetch BldSlt " sqlcode ":" sqlstate ; call tstend; */
if sqlcode<>100 then do
SltCnt=SltCnt+1
if astpos=0 then inTest=inTest","tstval
else lkTest=lkTest" or u LIKE "tstval
/* say inTest "/\" lkTest; call tstend; */
end
end
execsql "close BldSlt"
if SltCnt=0 then exit 99
if inTest='' then parse var lkTest . lkTest /* drop "or" */
else inTest="u IN ("overlay(" ",inTest,1)")"
/*se inTest="u IN ("strip(inTest,"L",",")")" */
/* say inTest "::" lkTest; call tstend; */
slt="select * from qtemp/userlist where "inTest lkTest
say "slt="slt ; call tstend;
execsql "prepare SltUserP from :slt"
/* say "prepare SltUserP" sqlcode ":" sqlstate */
execsql "declare SltUser cursor for SltUserP"
/* say "declare SltUser " sqlcode ":" sqlstate */
execsql "open SltUser"; if rc<>0 then exit rc
/* say "open SltUser " sqlcode ":" sqlstate */
do while sqlcode<>100
execsql "fetch SltUser into :slt";
/* say "fetch SltUser" sqlcode ":" sqlstate ; call tstend; */
if sqlcode<>100 then say slt
end
execsql "close SltUser"
exit
tstend: procedure
say "Type HX to Halt Execution"
parse upper pull r
if r='HX' then exit; else nop
return
setup: procedure
execsql "drop table qtemp/userlist"
execsql "drop table qtemp/userslt"
execsql "create table qtemp/userlist (u varchar(10) allocate(8))"
execsql "create table qtemp/userslt (s varchar(10) allocate(8))"
execsql "insert into qtemp/userlist values",
" ('BOB'),('CHARLIE'),('DAVID'),('JACK'),('JOHN'),('MICHAEL')",
",('RICHARD'),('SAMUEL'),('SARAH'),('TOM'),('TOMMY'),('TORI')"
execsql "insert into qtemp/userslt values",
" ('BOB'),('DAVE'),('J*'),('TOM*')"
return
</code>
Regards, Chuck
midrange.com
