Complex SQL statements with parameters

[ctiscar]

Hi,

I'm using iReport 3.1.0 and I havo some problems using parameters in SQL statements with subquery in where clause. Example:

select a, b, c,

   from T1

   join t2 on (<join condition>)

 where t1.a = $P{xxx}

    and t2.x in (select x

                           from T3

                          where t3.y = $P{xxx})

The same query without the second condition (and t2.x in (select...)), perfectly runs without errors. Why this happens? Where is my mistake? Thanks

CT

This is the error log I get:Error filling print... Error executing SQL statement for : report4
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : report4 
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:141) 
    at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:668) 
    at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:588) 
    at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1193) 
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:843) 
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:792) 
    at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:63) 
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:402) 
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:234) 
    at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:890) 
    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-00936: missing expression
 
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) 
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) 
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573) 
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891) 
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830) 
    at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391) 
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672) 
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589) 
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527) 
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:135) 
    ... 11 more 
Print not filled. Try to use an EmptyDataSource...
 

 

[ctiscar]

I'm sorry for the fonts used!

[iancr01]

Have you tried where t3.y = $P!{xxx} ?

(note the exclamation point)

[ctiscar]

Great! It works fine.

The correct SQL statement is:

select a, b, c,

   from T1

   join t2 on (<join condition>)

 where t1.a = '$P!{xxx}'

    and t2.x in (select x

                           from T3

                          where t3.y = '$P!{xxx}')

because t1.a and t3.y are varchar2 Oracle type and Oracle expects a quoted string.

Thanks a lot.

CT

 

[colighto]

 I have also encountered the same problem and I was able to resolve it this is what I did:

The query of your main report should only return 1 result, I'm using ms sql server 2000 so what I did was "select top 1 * from inventory" this will only return the first result of the query that was being generated, if you are using a different type of rdbms try to search for its documentation and know how you will do this.