Query with IN clause and parameters

[bloubite]

Hi guys /tools/fckeditor/editor/images/smiley/msn/regular_smile.gif

In my report, I'd like to execute this query :

SELECT * FROM TABLE WHERE CODE_ITEM IN ( "AA", "BB", "CC")

As long as "item codes" are hardcoded in the query, everything is fine. But when I try to pass these by parameter, it doesn't work.

I get not result. Yet, when I try to display the parameter on the page, everything seems to be ok.

In my JRXML file, the query is :

SELECT * FROM TABLE WHERE CODE_ITEM IN ( $P{itemCodes} )

In my java file, the parameter is :

map.put("itemCodes", ""AA", "BB", "CC"");      (with backslashes that are not displayed on this forum)

The value expression is set to "Text" (java.lang.String) for the parameter.

I'm confused. Have you an idea ?

I'm not sure datasets can help for this kind of problem

Thanks in advance !

Post Edited by bloubite at 07/24/2011 20:15

Post Edited by bloubite at 07/24/2011 20:17

[danielferreira]

Hey instead of

 

SELECT * FROM TABLE WHERE CODE_ITEM IN ( $P{itemCodes} )

 

put

 

SELECT * FROM TABLE WHERE CODE_ITEM IN ( $P!{itemCodes} )

 

it should run perfectly

 

[szaharia]

Hi,

Using the $P{} syntax here does not behave as expected, because the parameter here is considered as the single string value '"AA", "BB", "CC"'.

To get the proper result here, try to use either the $P!{} sintax, or, more appropriate the $X{IN,...} function. More info about parametrized queries you can find here.

Hope this helps,

sanda

[jasperoily]

 Have two parameters as a workaround for this.

For example, get the list of values into Param1

Create another Parameter , Param2

In the Param2 'Default Value Expression' create the WHERE clause like below

" where  columnName IN(" + $P{Param1} + ")"

Param 2 should not have "Use as a prompt" checked

In the filter construct the WHERE clause as below

SELECT * FROM

$P!{Param2}

Do not forget to use ! while referring to Param2 in the filter.

[bloubite]

hi guys /tools/fckeditor/editor/images/smiley/msn/regular_smile.gif

 

$P!{}  is exactly what I was looking for.

 

Thanks for the quick answer !