(urgent) multi value on "in operator of where cause"

[hkjang]

Hi

I have a question regarding the parameter that is put with "in operator" on SQL.
I like to use multi parameter, so I like to utilize "in operator" on SQL.
For example, SQL in ireport will be

"select emp_name from employee where emp_id = $P{emp_id}" (tried type: java.math.BigDecimal or java.lang.String)

I defined one parameter emp_id on ireport. (class type : java.lang.String)

When I run the report with single value like "111" it is run okey,
But if I run with multi value like "111, 222" I got error message as below

"ORA-01722: invalid number"

Would you please advice how could I avoid error message?
Any advice will be appreciated,

Thanks in advice,
HKJang
 

[biaggi]

The query you are executing is

 

select emp_name from employee where emp_id = 111,222

 

So oracle fails, you should try using more than one parameter in your query.

[Gaby38]

As for me, I should try :

"where emp_id in $P!{emp_id}"  with emp_id="111,222" (java string) (notice the "!" in $P!{emp_id})

[biaggi]

What means that character in that position? :S

 

i would just write

 

"where field in ($P{Value})"

[Gaby38]

As far as I can understand, if you use "$P{param}" the parameter will be expanded according to its definition inside the SQL. For instance a string wille be expanded with the quotes ('string').

If you use $P!{param}, the parameter will be expanded as is.

Sorry, in my prvious post I did a mistake : the parameter, for having the correct SQL syntax, must include the parenthesis :

= "(111,222,333)"

I already used this formulation and it worked

Your suggestion seems OK but I didn't test it : but you have to use the $P! formulation as explained above :

" in ($P!{param})"

[hkjang]

Hi Gabriel!

Thanks for the advise, I could fixed my issue according to your direction.

Best Regards,
Seesang

[swood]

There is also the $X syntax, which automatically manages IN clauses. This is part of JasperReports.

 

    <parameter name="collection" isForPrompting="true" class="java.util.Collection"/>
...

    <queryString><![CDATA[

SELECT columns...

FROM tables....

where $X{IN, column, collection}

</queryString>
 

This automatically generates the right IN clause.

 

 

Sherman

Jaspersoft