Re: SQL find a string in multiple fields

On 12-Oct-2016 11:59 -0500, Gerald Magnuson wrote:

On 12-Oct-2016 11:47 -0500, Gerald Magnuson wrote:

I have a "search string" field to scan over multiple fields...

currently we are doing this with RPG example:

if s1xcnt = *blanks or
%scan(%trim(s1xcnt):%xlate(lw:up:mnujob)) > 0 or
%scan(%trim(s1xcnt):%xlate(lw:up:mnuonm)) > 0
;

I am attempting to do this in a where clause, but not getting it right

where UPPER(mnujob) like('%.......%)
or UPPER(mnuonm) like(%......%)

I have tried doing various %trim commands,
I have tried doing this as both a Prepared SQL statement, and inline.
<<SNIP>>

let's say S1xcnt = 'INVOICE '

So S1xcnt is perhaps the equivalent of one of, in RPGLE; probably the latter, given the use of %trim()?:

Dcl-S S1xcnt varchar(21) inz('INVOICE') ;

Dcl-S S1xcnt char(21) inz('INVOICE') ;

and
a list of mnujob ('BIL555','INV052','INVOICE010',)
a list of mnuonm ('Invoice Inquiry ', 'Shipment update ',
're-send Invoice ')

Hmm. Not sure what data type a /list/ is, as shown. The context for usage from the OP, appeared to show the variable mnujob referenced as a character string data type, so would that /list/ have been assigned as?:

mnujob = '''BIL555'',''INV052'',''INVOICE010'',' ;


Regardless, I offer the following which might be useful, perhaps to someone reading the archives:

A fully functional program, that can show the differences [which may or may not be preferable] or the matching effects, for a given input, when searching for the data using each of the method from the OP, an SQL LIKE, or an SQL LOCATE:

H dftactgrp(*no) actgrp(*NEW)
D* CRTSQLRPGI OBJ(qtemp/scantest) SRCFILE(...) SRCMBR(...)
D* OBJTYPE(*PGM) GENLVL(10) REPLACE(*YES) DBGVIEW(*SOURCE)
D scanTest PR
D inpString 21A
D scanTest PI
D inpString 21A

D S1xcnt S 21A
D mnujob S 85A varying
D mnuonm S 85A varying
D fndRPGscn S N
D fndSQLlike S N
D fndSQLloc S N
D dsplyTxt S 45A
D lw C 'abcdefghijklmnopqrstuvwxyz'
D up C 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

C/free
Exec SQL
Set Option Commit=*NONE, DatFmt=*ISO, SrtSeq=*LANGIDSHR ;

S1xcnt = inpString ;
mnujob = '''BIL555'',''INV052'',''INVOICE010'',' ;
mnuonm = '''Invoice Inquiry '', ''Shipment update '',' +
'''re-send Invoice '')' ;

if s1xcnt = *blanks
or %scan(%trim(s1xcnt):%xlate(lw:up:mnujob)) > 0
or %scan(%trim(s1xcnt):%xlate(lw:up:mnuonm)) > 0 ;
foundRPG = *ON ;
endif ;

Exec SQL
VALUES
case when :S1xcnt = ''
or rtrim(:mnujob) like
'%' concat trim(:S1xcnt) concat '%'
or rtrim(:mnuonm) like
'%' concat trim(:S1xcnt) concat '%'
then '1'
else '0'
end
INTO :fndSQLlike
;

Exec SQL
VALUES
case when :S1xcnt = ''
or locate( trim(:S1xcnt), rtrim(:mnujob) ) > 0
or locate( trim(:S1xcnt), rtrim(:mnuonm) ) > 0
then '1'
else '0'
end
INTO :fndSQLloc
;
dsplyTxt = 'RPGscan['+fndRPGscn+']SQLlike['+fndSQLlike
+']SQLloc['+fndSQLloc+']' ;
dsply dsplyTxt ;
*inlr = *on;


Following are some sample runs, different inputs, with the output displayed for each as the line following the CALL invocation:

call scantest 'BOGUS'
DSPLY RPGscan[0]SQLlike[0]SQLloc[0]

call scantest 'INVOICE'
DSPLY RPGscan[1]SQLlike[1]SQLloc[1]

call scantest 'invoice'
DSPLY RPGscan[0]SQLlike[1]SQLloc[1]

call scantest 'InVoice'
DSPLY RPGscan[0]SQLlike[1]SQLloc[1]

call scantest ''
DSPLY RPGscan[1]SQLlike[1]SQLloc[1]