using $X in the query, Parameter type not supporte

0
Hello,

A quick question - I'm attempting to create a report via iReport and set up the query similar to this:

SELECT FISCAL_YEAR,
ACCT_STRUCTURE,
ACCT_REGION,
ACCT_STATUS_CODE,
ACCT_STRUCTURE_DESC
FROM ACCOUNT_STRUCTURE A
WHERE $X{IN, ACCT_REGION, $P{REGION}}
AND LOCAL_STATUS = 'O'

I set up the parameter REGION with class type of: java.util.Collection

The error in iReport says that "Parameter type not supported in query : REGION class java.util.Collection"

I've also tried changing the parameter class to java.util.Arrays and java.util.List with the same results.

Any tips on how to correct would be greatly appreciated - thanks!
alchavez's picture
Joined: May 17 2007 - 2:03am
Last seen: 12 years 2 months ago

12 Answers:

-1
It might be better to create a simple string list such as "1,4,6,7,5,4,100" or "'CA','NY','MA','FL'"


and then use it in an SQL 'IN' context, like this:


SELECT FISCAL_YEAR,
ACCT_STRUCTURE,
ACCT_REGION,
ACCT_STATUS_CODE,
ACCT_STRUCTURE_DESC
FROM ACCOUNT_STRUCTURE A
WHERE ACCT_REGION IN ($P!{REGION})
AND LOCAL_STATUS = 'O'



Of course you'll need to make sure that there is at least one value in the list, otherwise your report will die ungracefully.


.

Post edited by: jmurray, at: 2007/10/10 00:09
Post edited by: jmurray, at: 2007/10/10 00:10
jmurray's picture
549
Joined: Dec 11 2006 - 11:19am
Last seen: 12 years 7 months ago
0
Thanks for the suggestion, I think I will use that method instead of using the $X function. If anyone has any examples of the $X function working, that'd be great. This should work well for me in the meantime.
alchavez's picture
Joined: May 17 2007 - 2:03am
Last seen: 12 years 2 months ago
2
Hi,
To make it work with your example, simply do it like:
SELECT FISCAL_YEAR,
ACCT_STRUCTURE,
ACCT_REGION,
ACCT_STATUS_CODE,
ACCT_STRUCTURE_DESC
FROM ACCOUNT_STRUCTURE A
WHERE $X{IN, ACCT_REGION, REGION}
AND LOCAL_STATUS = 'O'

just remove the $P{}, as the $X already takes in account, that the third parameter will be a parameter.
The definition for $X is like:
$X{IN, dbColumnName, parameterName}
$X{NOTIN, dbColumnName, parameterName}
ptolomej's picture
Joined: Sep 14 2006 - 9:31pm
Last seen: 12 years 10 months ago
0
Hi,

ptolomej is correct about the syntax. The param inside the $X braces shouldn't have $P{} around it.

Here's another tip. Setting default values for parameters is a good habit. But it isn't exactly obvious with parameters of type java.util.Collection. Here are some examples:

java.util.Arrays.asList(new String[]{"String1", "String2"})

java.util.Arrays.asList(new java.lang.Integer[]{new java.lang.Integer(1), new java.lang.Integer(2)})

jmurray's workaround suggestion is correct. But in general I prefer the $X syntax. It's easier to populate with a Multi-select input control, and it gracefully handles the case where you select zero values automatically.

And a final bit of trivia: As of version 2.0.2 iReport lists java.util.Collection in the dropdown list of Parameter Class Types. Of course with older versions of iReport you could just type it in. But it's nice to have it there by default as a reminder.

-Matt
JasperSoft
mdahlman's picture
8940
Joined: Mar 13 2007 - 2:43am
Last seen: 4 years 8 months ago
0

hi matt,

i need to set a default value for my parameter.. but the syntax mentioned by you just creates a fixed size list and i cannot add anything further to it.. pls help

madhukiran's picture
Joined: May 20 2009 - 1:53am
Last seen: 8 months 3 weeks ago
0

sorry i couldnt see the obvious choice: just use new ArrayList()

madhukiran's picture
Joined: May 20 2009 - 1:53am
Last seen: 8 months 3 weeks ago
0

Hello,

i would like to get back to this issue because I have a similar problem. We are about to migrate our reports from Jasperreports version 3.3 to 4.0.1. Some of the reports work just fine without any modifications. But we have some reports which do not pass the query verifier due to the "Parameter type not supported in query" check. The problem is that we had implemented the query just the way it was suggested in this thread (using !P{}). But it turned out that our parameter list - in some cases grew to big. So it blasted the maximum stringlength of the parameter in the "in clause". At that time we solved the problem by defining a special type in our Oracle database and calling a stored procedure which uses this type as an input parameter.  The value which is passed in is of type oracle.sql.ARRAY. It needs to be created via the database itself. We use a scriptlet for that. This solution worked fine in 3.3  and obviously passing an object of that type in the query actually makes sense. Now it does not work any more.

Are there any suggestions that would help me find a quick solution?

 

Thanks a lot in advance

Thorsten

Code:
SQL:
select
* from SOME_TABLE
where ID in (select * from TABLE($P{requestIdArray}))
 
 
Scriptlet code:
public void afterReportInit() throws JRScriptletException {
Connection connection = (Connection) this.getParameterValue("REPORT_CONNECTION");
ARRAY kiIdArray = null;
ARRAY kiIdArray2 = null;
try {
Map reportParameterMap = (Map) this.getParameterValue("REPORT_PARAMETERS_MAP");
Object arrayData = reportParameterMap.get("REQUEST_ID_ARRAY");
 
Long[] longArray = (Long[])arrayData;
 
ArrayDescriptor desc1 = ArrayDescriptor.createDescriptor("SPKTPRD.KID_TYPE",connection);
kiIdArray = new ARRAY(desc1, connection, longArray);
this.setVariableValue("requestIdArray", kiIdArray);
reportParameterMap.put("requestIdArray", kiIdArray);
 
Object arrayData2 = reportParameterMap.get("REQUEST_ID_ARRAY2");
 
if (arrayData2!=null)
{
 
Long[] longArray2 = (Long[])arrayData2;
 
ArrayDescriptor desc2 = ArrayDescriptor.createDescriptor("SPKTPRD.KID_TYPE",connection);
kiIdArray2 = new ARRAY(desc2, connection, longArray2);
this.setVariableValue("requestIdArray2", kiIdArray2);
}
 
} catch (SQLException e) {
e.printStackTrace();
}
}
</td></tr></tbody></table>
tschlat's picture
Joined: Apr 4 2011 - 1:25am
Last seen: 1 year 1 week ago
0

Meanwhile we have solved that issue by using a temporary table in which we insert the id's. Actually it is the same approach but without the necessity to pass a parameter into the stored procedure.

tschlat's picture
Joined: Apr 4 2011 - 1:25am
Last seen: 1 year 1 week ago
0

Hello everyone,

I trying to do the same of this topic and I am follow the indications, create a new List parameter, use $X in this way $X{IN, pan, CLIENTE}, I have a default value for the parameter but when I run the report this is the output
 

net.sf.jasperreports.engine.design.JRValidationException: Report design not valid : 
     1. Parameter type not supported in query : CLIENTE class java.util.List

 

I am use oracle. This is my query:

select
i.barcode,
t.opendate,
t.name,
t.pan,
t.client_address,
t.client_phone,
i.quantity,
i.amount,
i.unitamount,
t.pos,
t.localid,
t.ticketnumber
from tickets t
join ticketitems i on (t.pos = i.pos and t.localid = i.localid and t.ticketnumber = i.ticketnumber)
where
$X{IN, pan, CLIENTE}
order by t.opendate

 

Any idea??

Thanks in advance,

Caro.

 

carolopia's picture
Joined: Apr 6 2011 - 7:11am
Last seen: 3 years 3 months ago
0

hello,

I am trying to set a list of integers as a default parameter in ireport.

For example - as I would here:

query ...

where
    product_id in (1100, 1210)

This is working fine.

But if I try,

My parameter - mycollection (type - collection)

query ...

where
    $X(IN, product_id, mycollection)

default value, as per the above messages I am cut/paste exactly "java.util.Arrays.asList(new java.lang.Integer[]{new java.lang.Integer(1), new java.lang.Integer(2)})" into the default value expression ...

However this is not working - am I doing something wrong? 

help appreciated

supernerd's picture
Joined: Jun 4 2012 - 6:35am
Last seen: 7 years 1 month ago
0

managed to find something that works:

Arrays.asList(new Integer(1100), new Integer(1210))

or more simple

Arrays.asList(1100,1210)

supernerd's picture
Joined: Jun 4 2012 - 6:35am
Last seen: 7 years 1 month ago
0

When I use AND    $X{IN, i.id, ProjectID} in my MS SQL query part it showing the same error Parameter type not supported in query : ProjectID class java.util.Collection. How can I resolve this problem? I need to implement a multi select parameter in jasper report using a dynamically generated query which I already have.

mailtojustine's picture
Joined: Oct 29 2015 - 6:01am
Last seen: 1 year 12 months ago
Feedback
randomness