On 19-May-2011 08:29 , CRPence wrote:
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*')"
Using the same setup as in quoted text above and the same assumption
about both that the first asterisk indicating a generic prefix search
value precedes and that there is a limit of ten bytes for the queried
column and search values, the following query can produce the results
directly without any need to generate the query dynamically as was done
in the REXX of the prior reply:
with sltctl (len, cmp) as /* CTE of control file; limited slt */
( select int(min(nullif(locate('*',s),0)-1,length(s)))
, s /* replace(s,'*','') is prettier, but who's looking */
from qtemp/userslt )
select * from qtemp/userlist
where u in (select cmp from sltctl where len is null)
or left(u,1) in (select left(cmp,len) from sltctl where len=1)
or left(u,2) in (select left(cmp,len) from sltctl where len=2)
or left(u,3) in (select left(cmp,len) from sltctl where len=3)
or left(u,4) in (select left(cmp,len) from sltctl where len=4)
or left(u,5) in (select left(cmp,len) from sltctl where len=5)
or left(u,6) in (select left(cmp,len) from sltctl where len=6)
or left(u,7) in (select left(cmp,len) from sltctl where len=7)
or left(u,8) in (select left(cmp,len) from sltctl where len=8)
or left(u,9) in (select left(cmp,len) from sltctl where len=9)
For reference, the CTE result set with the setup data looks like:
LEN CMP
- BOB
- DAVE
1 J
3 TOM
*** End of data ***
Regards, Chuck
midrange.com
