Re: How to match a specific entry against a generic entry in a control table

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