String parameter in "in operator"

[raquel]

Hi

I have a String parameter that is put with "in operator" on SQL.

The query has (beetwen others) the following clauses:

where

...

AND ($P{IdOferta} = '0' OR OFERT_ID IN ($P!{IdOferta}) )

...

 

my code:

...

        String idOferta = req.getParameter("idOferta"); //    java.lang.String
        if (idOferta != null && !idOferta.equals("") && !idOferta.equals("")){
            parameters.put("IdOferta",idOferta);
        }
...

The problem is:

a) when I have idOferta=1234 everything is ok

b) when I have idOferta=1234,5678 there is no running error

c) when I have idOferta=1234,5678,9012 I get the following error:

net.sf.jasperreports.engine.JRException: Error executing SQL statement for : myReport]: java.sql.SQLException: ORA-01722: invalid number

Any suggestion?

Thanks in advance.

 

[raquel]

It could be Jasper thinks options a) and b) contains only one number?

a)1234

b) 1234,5678 (where 5678 are decimals)

this way, c) is not a valid number....

how can I solve it?

[svenn]

Try printing dOferta on the report  to see what is actually getting passed. Assuming that OFERT_ID  is a number you code looks ok. Maybe your not getting what you think you are getting.

[shrije]

Try ti run this query outside of jasper reports using any sql editor like TOAD or SQL developer. Use the same values. Problem may with the query itself with the specified parameter.

[raquel]

when I tried to write idOferta, I realised my server wasn't updating the report!!

The query

...

AND ($P{IdOferta} = '0' OR OFERT_ID IN ($P!{IdOferta}) )

...

was ok and parameters too.

Thanks for all the replies.