Re: Dynamic pattern with embedded SQL.

[CRPence <CRPbottle@xxxxxxxxx>]

Alan Campin wrote:
> I want to do the following. Am I going to have to create a
> dynamic SQL statement?
>
> Exec Sql Declare C1 Cursor For
>                    Select MLNAME,
>                              MLMTXT,
>                              MLSEU2
>                       From QTEMP/MBRLIST
>                       Where MLNAME Like :Pattern
>                       Order By MLNAME;
>
> Pattern has something like '%LL%'
>
> I have tried as above and
>
> '''%LL%'''
>
> They all return no records.

  Typically for MLNAME being CHAR(10), the correct pattern should be 
'%LL%%%%%%%' where the extra percent signs ensure the pattern match is 
not interpreted as '%LL%      ' which is, instead of what is desired, a 
request to find all rows that have 'LL' within the first four bytes of 
the string [because the last six must of course be spaces].  The noted 
as correct pattern will search for any rows that have 'LL' anywhere in 
the string.  IIRC if MLNAME is VARCHAR [original type or cast in the 
WHERE clause], the original described pattern will function as expected. 
 FWiW there may also be a QAQQINI option which makes the host variable 
be interpreted as variable; or perhaps I am confusing with some other 
memory of host variables and the INI.?

Regards, Chuck