JUST DO IT ~

我只想当个程序员

ireport sql where List in 多个值 jasper

 

ireport List in where  


 <parameter name="p1" class="java.util.List"/>
 <queryString language="SQL">
  <![CDATA[select * from user2 where   $X{IN, name , p1}]]>
 </queryString>


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

 

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

Gaby
              
 Gaby38
 
Project Roles
Posts: 248
 

Karma: 35
 
 
    
 
 #66426 
RE:Issue with Parameters that are from a List 11/06/2009 11:15    
So when i pass the parameter to the report i have to place a () before and after teh parameters 
 
 

 

 

 

How to do multi-select in iReport 3.6 09/10/2009 19:42    
I am trying to run a report with a multi-select parameter in iReport 3.6 but cannot get it to run. Parameter name is EmployeeType of type java.util.Collection. My default value expression is java.util.Arrays.asList(new String[]{"Manager","General"})

select Name
from Table1
where $X{ IN, EmpType, EmployeeType}

In the Report Query window, I get an error message saying:

Error: SQL problems: Line 3: Incorrect syntax near 'X'.


If I try using $P I get the message:

Error: SQL problems: The Java type java.util.Arrays$ArrayList is not a supported type.


If I try using $P! I get the message:

Error: SQL problems: Invalid column name 'Manager, General'.

Any ideas on how to solve this?
              
 zamudio
 
Project Roles
Posts: 4
 

Karma: 1
 
 
    
 
 #63637 
RE:How to do multi-select in iReport 3.6 09/10/2009 20:31    
zamudio,


That's a bug in the "Report query" tab. Please enter a bug for it in the tracker.


The work around is simple enough, but it's certainly annoying:
1. Do not use "Automatically Retrieve Fields" (I hate that option anyway.)
2. Remove the $X syntax; click Read Fields; add the $X syntax back.


The report works fine with $X syntax (both in iReport and in JasperServer). It's only the query panel that doesn't work correctly.


Regards,
Matt
              
 mdahlman
 
Project Roles
Posts: 790
 

Karma: 153
 
 
    
 
 #63641 
RE:How to do multi-select in iReport 3.6 09/10/2009 21:49    
That works!

One more question, how do you pass a Collection field to a dataset or a subreport?

zamudio
              
 zamudio
 
Project Roles
Posts: 4
 

Karma: 1
 
 
    
 
 #63730 
RE:How to do multi-select in iReport 3.6 09/12/2009 21:42    
Found it!

To pass a Collection parameter to a subreport or dataset enter it as the following expression:

If your report language is Java, pass the parameter as $P{ParameterName}

If your report language is Groovy, pass the parameter as $P{ParameterName}.toArray()

zamudio

 

Post Edited by zamudio at 09/12/2009 22:02               
 zamudio
 
Project Roles
Posts: 4
 

Karma: 1
 
 
    
 
 #70420 
RE:How to do multi-select in iReport 3.6 02/01/2010 17:06    
hi there!

i'm trying to do the same thing as zamudio but in my case, the values are Integer.

i created a parameter called "yearsCollection" of type collection and the Default Value set to new ArrayList(Arrays.asList( new Integer[]{new Integer(2008),new Integer(2009),new Integer(2010)} ))

i have a column in my database called "year" of type int..

i passed the parameter value to the query like this:

$X{ IN, year, yearsCollection}

when i run the report and in the prompt window i click in the "Use Default" button, the reports fills correct, but when i try to set values in the prompt text field (ex: 2008,2009) it crashes and the following error appears:

Implicit conversion from datatype 'CHAR' to 'INT' is not allowed.

=/, i read somewhere that the jasper assumes that the values datatypes passed to the $X{ IN, year, yearCollection} are supposed in the runtime, so i don't know how to manage this issue...

 

i also tried this way: "year IN ($P!{yearCollection})" with no success

i'm using iReports 3.7 and JasperServer 3.7

 

i hope someone can help me!
 
 
 

posted on 2010-12-08 00:11 小高 阅读(2879) 评论(1)  编辑  收藏 所属分类: java基础OracleJ2EE

评论

# re: ireport sql where List in 多个值 jasper 2014-12-09 11:18 lul

test ok! thanks!
use this "select * from table where condition in ($P!{list})"
list like "1,2,3" or "1,2,3",the ireport can't to know they difference.  回复  更多评论   


只有注册用户登录后才能发表评论。


网站导航:
 

导航

<2010年12月>
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678

统计

常用链接

留言簿(3)

随笔分类(352)

收藏夹(19)

关注的blog

手册

搜索

积分与排名

最新评论

阅读排行榜

评论排行榜