Passing wildcards in a parameter in LIKE Statement

[emillerphx]

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!

 

 

[CaptnTony]

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.

[emillerphx]

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

 

[CaptnTony]

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