using $X in the query, Parameter type not supporte

[alchavez]

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!

[jmurray]

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

[alchavez]

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.

[ptolomej]

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}

[mdahlman]

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

[madhukiran]

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]

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

[tschlat]

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_TABLEwhere 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();}}

[tschlat]

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.

[carolopia]

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.

 

[supernerd]

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]

managed to find something that works:

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

or more simple

Arrays.asList(1100,1210)

[mailtojustine]

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.