No such property? query help

[manshack_one]

 This is my query:

$P{completed}==Boolean.TRUE?

"select a.AuditDate, a.UnitName, a.Auditor, b.NumReview, b.NumApplicable, b.NumCompliant, b.NumIncompliant, b.Compliant, b.NA, c.qnum, c.question, c.discipline, c.type from ORA_AUDIT a join ORA_DETAIL b on a.AuditID = b.AuditID join ORAQ c on b.QID = c.pk where a.AuditDate = $P{auditDate};"
:
"select s.AuditDate, s.UnitName, s.Auditor, d.NumReview, d.NumApplicable, d.NumCompliant, d.NumIncompliant, d.Compliant, d.NA, s.qnum, s.question, s.type, s.discipline from( select a.AuditID as aid, a.AuditID, a.AuditDate, a.UnitName, a.Auditor, q.pk as qid, q.qnum, q.question, q.type, q.discipline from ORA_AUDIT a, ORAQ q) s left outer join ORA_DETAIL d on d.AuditID = s.aid and d.QID = s.qid order by s.aid, s.qid;"

It's placed into a variable so that I can run two separate queries based off of one checkbox input control.  Without the parameter $P{auditDate} it will run.  With the parameter I get an error message:

Version:0.9 StartHTML:-1 EndHTML:-1 StartFragment:0000000111 EndFragment:0000004171 Error filling print... Error executing SQL statement for : ORA_Summary_Worksheet
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : ORA_Summary_Worksheet 
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:143) 
    at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:686) 
    at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:606) 
    at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1273) 
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:892) 
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:841) 
    at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:58) 
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:417) 
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:247) 
    at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:858) 
    at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572) 
    at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997) 
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'parameter_auditDate.getValue())' at line 1 
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) 
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) 
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) 
    at java.lang.reflect.Constructor.newInstance(Unknown Source) 
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) 
    at com.mysql.jdbc.Util.getInstance(Util.java:381) 
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) 
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) 
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491) 
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) 
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) 
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) 
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542) 
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734) 
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885) 
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:137) 
    ... 11 more 

What am I doing wrong?  Are parameters not supported in the where statement of a join?

[pwd]

What data-type is your parameter?

[manshack_one]

 java.util.Date()

[manshack_one]

 I have 2 other parameters that need to be added which will be Collection datatypes.  It doesn't like those either.

This query runs fine without any parameters.  But I need to filter out the records appropriately so I'll have to include

them at some point.

[manshack_one]

 Thought I'd add some more info.  My report query is just a parameter $P{querychoice}.  It's a string and it's default value is set to :

$P{completed}==Boolean.TRUE?

"select a.AuditDate, a.UnitName, a.Auditor, b.NumReview, b.NumApplicable, b.NumCompliant, b.NumIncompliant, b.Compliant, b.NA, c.qnum, c.question, c.discipline, c.type from ORA_AUDIT a join ORA_DETAIL b on a.AuditID = b.AuditID join ORAQ c on b.QID = c.pk where a.AuditDate = $P{auditDate} and $X{IN,a.Auditor,auditorName} and $X{IN,a.UnitName,unitName};"

:

"select s.AuditDate, s.UnitName, s.Auditor, d.NumReview, d.NumApplicable, d.NumCompliant, d.NumIncompliant, d.Compliant, d.NA, s.qnum, s.question, s.type, s.discipline from( select a.AuditID as aid, a.AuditID, a.AuditDate, a.UnitName, a.Auditor, q.pk as qid, q.qnum, q.question, q.type, q.discipline from ORA_AUDIT a, ORAQ q) s left outer join ORA_DETAIL d on d.AuditID = s.aid and d.QID = s.qid order by s.aid, s.qid;"

 

I added back in the other two parameters I need which are 2 collection values.  Whether I put my report query as $P{querychoice} or $P!{querychoice}
I get the same result:

Version:0.9 StartHTML:-1 EndHTML:-1 StartFragment:0000000111 EndFragment:0000006097 Error filling print... Error evaluating expression : 
    Source text : $P{completed}==Boolean.TRUE?
"select a.AuditDate, a.UnitName, a.Auditor, b.NumReview, b.NumApplicable, b.NumCompliant, b.NumIncompliant,    b.Compliant, b.NA, c.qnum, c.question, c.discipline, c.type from ORA_AUDIT a join ORA_DETAIL b on a.AuditID = b.AuditID join ORAQ c on b.QID = c.pk where a.AuditDate = $P{auditDate} and $X{IN,a.Auditor,auditorName} and $X{IN,a.UnitName,unitName};"
:
"select s.AuditDate, s.UnitName, s.Auditor, d.NumReview, d.NumApplicable, d.NumCompliant, d.NumIncompliant, d.Compliant, d.NA, s.qnum, s.question, s.type, s.discipline from( select a.AuditID as aid, a.AuditID, a.AuditDate, a.UnitName, a.Auditor, q.pk as qid, q.qnum, q.question, q.type, q.discipline    from ORA_AUDIT a, ORAQ q) s left outer join ORA_DETAIL d on d.AuditID = s.aid and d.QID = s.qid order by s.aid, s.qid;"
net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : 
    Source text : $P{completed}==Boolean.TRUE?
"select a.AuditDate, a.UnitName, a.Auditor, b.NumReview, b.NumApplicable, b.NumCompliant, b.NumIncompliant,    b.Compliant, b.NA, c.qnum, c.question, c.discipline, c.type from ORA_AUDIT a join ORA_DETAIL b on a.AuditID = b.AuditID join ORAQ c on b.QID = c.pk where a.AuditDate = $P{auditDate} and $X{IN,a.Auditor,auditorName} and $X{IN,a.UnitName,unitName};"
:
"select s.AuditDate, s.UnitName, s.Auditor, d.NumReview, d.NumApplicable, d.NumCompliant, d.NumIncompliant, d.Compliant, d.NA, s.qnum, s.question, s.type, s.discipline from( select a.AuditID as aid, a.AuditID, a.AuditDate, a.UnitName, a.Auditor, q.pk as qid, q.qnum, q.question, q.type, q.discipline    from ORA_AUDIT a, ORAQ q) s left outer join ORA_DETAIL d on d.AuditID = s.aid and d.QID = s.qid order by s.aid, s.qid;" 
    at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:195) 
    at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:589) 
    at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:557) 
    at net.sf.jasperreports.engine.fill.JRFillDataset.setFillParameterValues(JRFillDataset.java:638) 
    at net.sf.jasperreports.engine.fill.JRFillDataset.setParameterValues(JRFillDataset.java:586) 
    at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1272) 
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:892) 
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:841) 
    at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:58) 
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:417) 
    at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:247) 
    at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:858) 
    at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572) 
    at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997) 
Caused by: groovy.lang.MissingPropertyException: No such property: X for class: ORA_Summary_Worksheet_1274128077191_632086 
    at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:49) 
    at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:59) 
    at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.invokeMethodN(ScriptBytecodeAdapter.java:169) 
    at ORA_Summary_Worksheet_1274128077191_632086.getProperty(calculator_ORA_Summary_Worksheet_1274128077191_632086) 
    at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.getGroovyObjectProperty(ScriptBytecodeAdapter.java:532) 
    at ORA_Summary_Worksheet_1274128077191_632086.evaluate(calculator_ORA_Summary_Worksheet_1274128077191_632086:161) 
    at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:182) 
    ... 13 more 

[manshack_one]

 Here is my jrxml file.  I moved the sql with the parameters back to the report query and still get the same error.

 

What am I overlooking?

 

 

[manshack_one]

 I realized I was still getting the error because I was choosing "true" for my $P{completed} parameter.  By moving the string from that true clause
into the report query I found that it was still having trouble with the same error.  I gave $P{completed} a value of false and it ran the report so it's something
to do with the string class for that parameter.  Am I not formatting it correctly?
 

[manshack_one]

 iReport definitely isn't liking parameters inside parameters for this string.  If I choose true for $P{completed}
even with the collection parameters removed it still bombs on the $P{auditDate}.

This is crazy.

[Giulio Toffoli]

In facts this is not possible. The value of the parameter is used AS IS, and its value is not re-processed as expression (which would be crazy in effects, don't you think?)

There is not an easy solution to your problem. The only solution I have in mind would to define the query string as:

$P!{SQL_CHUNK_1}  $P{PARAM_1}   $P!{SQL_CHUNK_2}  $P{PARAM_2} $P!{SQL_CHUNK_3} $P{PARAM_3} $P!{SQL_CHUNK_4} 

and configure the individual chunks to accept parameters with no effects when they are not used. 

Giulio

[manshack_one]

 I think I'm just going to make a copy of the report.  One for each query.
The only real difference between the two queries are the joins so that I get
either completed data or nulls for blank data.  

Thanks for clearing that up though.  It was driving me nuts.