ireport sql where List in 多个值 jasper

ireport List in where

create table user2 (
id varchar2(3),
name varchar2(5)
);
insert into user2 values('1','tom');
insert into user2 values('1','ask');
insert into user2 values('3','tom2');
insert into user2 values('3','a');

expression is

new Boolean($P{p1}.add($F{your_field_from_query})

keep the rest type as report so that all the values from the field get added into the para p1

--> now use it in your where clause as follows:

where $X{IN,col_name,p1}

Lee,

I apologize! I was wrong because I mixed the two cases : string parameter and collection!

I first tried with a collection and , as far as I remember, it worked but as I said I needed to pass the parameters through an URL.

The $X{} parameter form needs effectively a collection.

What I used for the string parameter is the $P!{} form (notice the !) which expands the parameter as it.

So in your query you should try something like that :

" where table.value in $P!{A}" if A contains the string "(1,2,3)"

I am sure I used it and it worked. Now I am not in the environment where I can access my jasperserver so I cannot do cut and paste of the real query I used.

As soon as I am in the good environment, I will post you more details !

Gaby38

Project Roles
Posts: 248

Karma: 35

#44638
RE:Issue with Parameters that are from a List 08/14/2008 17:56
Lee,

I confirm my previous post. I have a report under jasperserver which is launched via URL and which has one of his parameters "I_ActList" which is a list of "activities id" passed as a string and in my query I have in the where clause :

"where stat.QUARTIER_Adresse.GdQuartier=$P{GdQuartier}
and conso_jour.id_act in $P!{I_ActList}"

THis is a copy/paste of a part of my query viewed under iReport.

I_ActList contains a comma separated list of integers which are the ids of activity fields :

"(6,7,8)" for instance

and it works.

Hope you will success in your environment

http://stage.jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=70494

http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=44570

org.apache.commons.lang.StringUtils.join($P{A}.toArray(),",")

http://www.daniweb.com/forums/thread143304.html

http://www.javalobby.org/java/forums/t43616.html

http://www.blogjava.net/killme2008/archive/2007/02/06/98242.html
http://jtri.sourceforge.net/en/report/ireportHibernate.html

http://netspirit.javaeye.com/blog/144057