Getting SQL error ORA-01722: invalid number

[rajajasper]

Hi All,

I havee iReport sql like this. se;ect * from site where reg_id in ($P{Region_id}).

I have drop down Region_id  1, Region_id 2, Region_id  3, Region_id 4 and ALL. If user select ALL parameter will return (1,2,3,4) regions in $P{Region_id}. I have declared  $P{Region_id} as java.lang.String. If $P{Region_id} is slects 1 or 2 or 3 or 4 report works fine.when select all Region_ids the report is throwing below exception.

 

Error filling print... Error executing SQL statement for : Site Cert Expiration Listing
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Site Cert Expiration Listing     
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:141)     
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:685)     
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:605)     
at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1235)     
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:865)     
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:814)     
at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:63)     
at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:421)     
at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:251)     
at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:898)     
at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:561)     
at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:986) 
Caused by: java.sql.SQLException: ORA-01722: invalid number

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)     
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)     
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)     
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)

 

Can Any one give solution for this how to return data from sql when user selects All regions in SQL sql IN clause.

Thanks in advance.

Raja

[svenn]

Use $P!{Region_id} 

[rajajasper]

Wow its working......................

Thanks for your help