iReport 2.0.4: do string type parameters not work in SQL queries?

1
votes

I'm working on a report in iReport 2.0.4 connecting to an Oracle database through oracle.jdbc.driver.OracleDriver that returns a list of entries based on union membership. The report takes a start date, end date, and union name as parameters. The basic structure of the query is

select 
    ...
from
    ...
where
    union_opt_in_dt <= $P{EndDate} and
    union_opt_out_dt >= $P{StartDate} and
    union_name = $P{UnionName}
...

where StartDate and EndDate have type java.lang.Date and UnionName has type java.lang.String.

The problem is that, if I use the UnionName parameter and pass in the union name (example "AFSCME"), the query returns no data. If I remove the UnionName parameter and hardcode the union name, as in

where
    union_opt_in_dt <= $P{EndDate} and
    union_opt_out_dt >= $P{StartDate} and
    union_name = 'AFSCME'

then I get the rows I expect for that union name.

So why do the date parameters work, but not the string parameter? Is there a method I need to call on the string parameter (such as .trim()) to get it to work?

UPDATE, FIXED

Found an answer to a different question on another forum that fixes it; I have to do an immediate substitution (terminology?) of the parameter in the query, so it needs to be

where
    union_opt_in_dt <= $P{EndDate} and
    union_opt_out_dt >= $P{StartDate} and
    union_name = '$P!{UnionName}'

That works. Not sure I understand the logic of it (as of a week and a half ago I'd never heard of Jasper Reports), but at least I can move forward.

UPDATED UPDATE, NOT FIXED

Okay, so the above solution isn't quite what I need; it only substitutes the default value, not necessarily what's passed in. Argh.

Why do the dates work, but not the union name? I've made sure that the value in the parameter exactly matches what's in the database, character for character.

1
ireport

1 Answers

0
votes

Try trimming the UnionName before passing.