Re: Dynamic pattern with embedded SQL.

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