problems with IN condition in query report

[Icarus]

I have errors like

-Incompatible java.util.LinkedList value assigned to parameter countryCodes in the REPORT_NAME dataset

- if I send the values like a string '1, 2, 3' I get error 'error casting to Integer'

 

is this condition supported by iReport query ? or do I have to make a variable with as many ORs as my list have ?

 

thanks for your time

 

PD: iReport version 3.5.3

Code:

SELECT CODE, NAME FROM CLIENTS

 WHERE CODE = $P{clientCode}

   AND COUNTRY IN $P{countryCodes}

[Icarus]

well, surfing (with the alien) around I found the way to make the IN statement

SELECT CODE, NAME FROM CLIENTS
 WHERE CODE = $P{clientCode}
   AND $X {IN, COUNTRY, countryCodes}

but, now I got this error result:

11:28:43,618 ERROR [sTDERR] Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: La pseudocolumna "$X" no es válida.
11:28:43,619 ERROR [sTDERR]     at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
11:28:43,619 ERROR [sTDERR]     at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
11:28:43,619 ERROR [sTDERR]     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
11:28:43,620 ERROR [sTDERR]     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source)
11:28:43,620 ERROR [sTDERR]     at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
11:28:43,621 ERROR [sTDERR]     at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
11:28:43,621 ERROR [sTDERR]     at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
11:28:43,621 ERROR [sTDERR]     at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
11:28:43,622 ERROR [sTDERR]     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
11:28:43,622 ERROR [sTDERR]     at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
11:28:43,622 ERROR [sTDERR]     at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:135)
11:28:43,623 ERROR [sTDERR]     ... 93 more

Code:

[slow]

you can chosso several trick to avoid this problem...

the first is to pass a String in the format (0,1,2,3,4...) in the sql using the $P!{} syntax

 

SELECT CODE, NAME FROM CLIENTS WHERE CODE = $P{clientCode}   AND COUNTRY IN $P!{countryCodes}

the second is to use a scriplet that converts a List (in which you have the data you want) in the format ('0','1','2','3','4'...) etc.
to do this you can use this general method I create for my reports:

    public String convertToString(List list, String prefix, String separator, String delimiter, String suffix, String nullList) {
        if (list==null) {
            return nullList;
        }
        if (list.size()==0)
            return prefix+suffix;
        Iterator i = list.iterator();
        StringBuffer sb = new StringBuffer();
        sb.append(prefix+delimiter+i.next().toString()+delimiter);
        while (i.hasNext()) {
            sb.append(separator+delimiter+i.next().toString()+delimiter);
        }
        sb.append(suffix);
        return sb.toString();
    }

in this case:
prefix = "("
separator=","
delimiter="'"    (if you pass integer and not string values, the use "")
suffix=")"
nullList="()"

 

_________________________________________

if it works... give me KARMA points please!    : ) 
_________________________________________

listening: Nine Inch Nails - Hurt

[jbchurn]

Hi,

I use iReport 3.7.0 and below seems to work for me.

SELECT CODE, NAME FROM CLIENTS
WHERE CODE = $P{clientCode}
  AND $P!{countryCodes}    So, you will pass "country IN (1,2,3)" without quotes to parameter countryCodes.

HTH.