SQL0418 - trying to use a parameter marker in conjunction with LIKE

[kreynolds]

I would like to be able to use a prepared statement as follows:

select *
from PAYPAL0
where plemail
like '%' concat cast(trim(?) as varchar(52)) concat '%'

I can get the statement to run in SQL using STRSQL but when I embed it into my RPG program I get the following error.

Message ID . . . . . . : SQL0418 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 10/03/16 Time sent . . . . . . : 08:07:14

Message . . . . : Use of parameter marker or NULL not valid.
Cause . . . . . : Parameter markers and NULL are not allowed:

Thoughts??

[B.Hauser]

Could you please post the complete statement.
It seems to me you try to use a parameter marker within static SQL, which is not possible. You may use a host variable instead. (and IMHO there is no need for this statement to be prepared dynamically)
If it is dynamic SQL you can use parameter markers, but the the single quotes have to be doubled.

Birgitta

[kreynolds]

Here is the complete prepared statement in my program:

stGetTransactions = 'select ifnull(plco#,'' ''), ' +
'ifnull(plord#, '' ''), ' +
'ifnull(addoct,'' ''), abname, date(plentz), ' +
'ifnull(plemail, '' ''), ' +
'pl$amt, ifnull(tdsdsc, '' ''), ' +
'ifnull(adrep#, '' ''), ifnull(rmpnam, '' '') ' +
'from PAYPAL0 ' +
'left join ARDTL0 on addrfd = pldrfd and ' +
'addrft = pldrft and adord# = plord# and ' +
'adtrnt <> ''NAT'' ' +
'left join REPMAS0 on adco# = rmco# and ' +
'adrep# = rmrep# ' +
'left join ORDMAS0 on ' +
'plco# = omco# and plord# = omord# ' +
'left join ADDMAS0 on omcab# = abadd# ' +
'left join tfcccd5 on tdco# = plco# and ' +
'tdcode = plcccd ' +
'where plco# in (?, ?) and ' +

// Email Address
'(? = '' '' or (? <> '' '' and ' +
'plemail like ''%'' ' +
'concat cast(trim(?) as varchar(52)) ' +
'concat ''%'') and ' +

// Order#
'(? = '' '' or (? <> '' '' and ' +
'plord# = ?)) and ' +

// Amount
'(? = 0 or (? <> 0 and pl$amt ' +
'between ? and ?)) and ' +

// Entry Date
'(? = ''0001-01-01'' or ' +
'(? <> ''0001-01-01'' and ' +
'date(plentz) >= ?)) and ' +

// Include voids or not
'((? = ''N'' and plcccd <> ''V'') ' +
'or ? <> ''N'')';

exec sql prepare sGetTransactions from :stGetTransactions;

[B.Hauser]

IMHO the CAST specification is the problem, just remove the cast and retry it.
Even though this statement is complex there is no need for dynamic SQL.

Birgitta

[kreynolds]

I tried changing that piece to this:
plemail like ''%'' concat trim(?) concat ''%''

I got the same error (SQLSTT = 42610). Maybe the problem is somewhere else in the statement??

Message ID . . . . . . : SQL0418 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 10/03/16 Time sent . . . . . . : 12:38:53

Message . . . . : Use of parameter marker or NULL not valid.
Cause . . . . . : Parameter markers and NULL are not allowed:

Another programmer and I tried using the following which also did not work:
like (select ''%'' || ? || ''%'' from SYSIBM.SYSDUMMY1)

[kreynolds]

I got it to work as follows using a varchar variable for the email address as noted below. Thank you for all your help!!

stGetTransactions = 'select ifnull(plco#,'' ''), ' +
'ifnull(plord#, '' ''), ' +
'ifnull(addoct,'' ''), abname, date(plentz), ' +
'ifnull(plemail, '' ''), ' +
'pl$amt, ifnull(tdsdsc, '' ''), ' +
'ifnull(adrep#, '' ''), ifnull(rmpnam, '' '') ' +
'from PAYPAL0 ' +
'left join ARDTL0 on addrfd = pldrfd and ' +
'addrft = pldrft and adord# = plord# and ' +
'adtrnt <> ''NAT'' ' +
'left join REPMAS0 on adco# = rmco# and ' +
'adrep# = rmrep# ' +
'left join ORDMAS0 on ' +
'plco# = omco# and plord# = omord# ' +
'left join ADDMAS0 on omcab# = abadd# ' +
'left join tfcccd5 on tdco# = plco# and ' +
'tdcode = plcccd ' +
'where plco# in (?, ?) and ' +

// Email Address
'(? = '' '' or (? <> '' '' and ' +
'plemail like (select ''%'' ' +
'|| ? ' + // For this parameter I used a varchar variable
'|| ''%'' from sysibm/sysdummy1))) and ' +

// Order#
'(? = '' '' or (? <> '' '' and ' +
'plord# = ?)) and ' +

// Amount
'(? = 0 or (? <> 0 and pl$amt ' +
'between ? and ?)) and ' +

// Entry Date
'(? = ''0001-01-01'' or ' +
'(? <> ''0001-01-01'' and ' +
'date(plentz) >= ?)) and ' +

// Include voids or not
'((? = ''N'' and plcccd <> ''V'') ' +
'or ? <> ''N'')';

exec sql prepare sGetTransactions from :stGetTransactions;

exec sql declare cGetTransactions cursor for sGetTransactions;