parameter in Report sql query with "like"

[2004 IR Help]

By: allan - allanpanaguiton

parameter in Report sql query with "like"

2004-08-27 02:31

Anybody who can help me with this type of sql?

 

select podm_id, podm_date, supp_name, podm_incharge

from tps_pomas

left outer join tco_supplier

ON podm_suppid = supp_id

where podm_suppid LIKE '% $P{SupId} %'

 

this entry will cause error because the LIKE with "%" (for wildcard) portion requires me to place a single quote " ' " on the syntax which causes the error.

 

Thanx for you help!! :-)

 

 

 

 

 

By: C-Box - c-box

RE: parameter in Report sql query with "like"

2004-08-27 03:46

Hi,

 

Have you already tried to put the whole query into a parameter and use that parameter in the query instead?

 

C-Box

 

 

 

 

By: Rick Millar - rmillar

RE: parameter in Report sql query with "like"

2004-08-27 09:25

Try ...LIKE '%$P!{SupId}%'

 

--don't forget to include the !.

 

Make sure SupId == "yourSupIdVal"

 

 

 

 

By: Mark Rhodes - mrhodes2

RE: parameter in Report sql query with "like"

2004-08-27 09:38

can you pass the % in as part of the parameter. Eg: $P{SupId} = new String("%123%").

 

Then your query is

 

... where podm_suppid LIKE $P{SupId}

 

This works for me when users are looking for descriptions. Also, I'm using MS SQL 2000. May not work in all flavors of SQL.