Passing wildcards in a parameter in LIKE Statement

0

I want to do a query for all projects with a given string in their project number. 

I am doing a query on MS SQL Server

The a query that works very well has:

WHERE projnum LIKE "%PAD%"

This gives me all projects with PAD in the name

OK, but of course I want the users to be able to pick a string.  I want to prompt the user for that string and put it nto a parameter: $P{PN}

So my SQL statement in iReport is:

WHERE projnum LIKE "%" + $P{PN} +"%"

This returns nothing.  No error, just didn't find any records.

What is really annoying, is if I use the copy to clipboard button in the Report Query window in iReport

and past it into dbVisualizer I get:

WHERE projnum LIKE "%" + 'PAD' +"%"

And that query works in dbVisualizer.

I'll admit, I don't know java well at all.  Is there some way to escape or double quote or something so that JasperReport makes the right query?

I've also tried setting $P{PN} to "%PAD%"  Same behavior.

A related question: how do I see what the actual query is that gets sent to my database whe I run it?

Thanks!

 

 

emillerphx's picture
Joined: Jan 31 2008 - 10:06pm
Last seen: 9 years 6 months ago

3 Answers:

0
WHERE projnum LIK '%' + '$P!{PN}' + '%' iReport will quite literally replace the contents of the $P!{PN} with your parameter, so since they are strings (Like '%xxxx%' for instance) you must have your parameter placed inside single quotes. Hope that helps.
CaptnTony's picture
Joined: Jul 19 2006 - 12:24am
Last seen: 11 years 1 month ago
0

My ignorance is embarassing.  That fixed it!  thanks.  Now I need to go and research so I understand what is going with ' vs " and $P{} vs $P!{}.

You have made a lot of project engineers happy.  They can now do wildcard searches on their cost accounts.

Eric

 

emillerphx's picture
Joined: Jan 31 2008 - 10:06pm
Last seen: 9 years 6 months ago
0

Hey, took me a full day of pulling hair out to think about that. Glad it helps out. Wait until they want to run stored procedures/functions :> I like those a LOT more than putting the code in the report. Just a thought.

.

.

Oh, as far as seeing what the query is when you 'run' it.  I haven't found a way to do that either.  I generally watch what it's doing by looking at the query profiler in MS SQL.



Post Edited by G. Todd Frahm at 12/04/08 15:13
CaptnTony's picture
Joined: Jul 19 2006 - 12:24am
Last seen: 11 years 1 month ago
Feedback