cbarlow3

I ended up using the following approach, which still allows me to let Null be the default for this parameter, but because this parameter is intended to identify the EndDate for a date range of qualifying transactions, I translate a Null to the date '2999-12-31'. Yes, it's my version of the Y2K problem, and I am ashamed, but until various versions of DB2, SQL, and iReport/Jasper* all treat parameter markers in a way that make Null an acceptable value to check for, that's my solution: "GL"."CATEGORY"='A' AND ("GL_ENTRY"."EFFECTIVE_DATE"<=$P{EndDate} OR COALESCE($P{EndDate},'2999-12-31')='2999-12-31')

I narrowed it down further...seems to be a difference in the implementation of SQL. when I took out the OR {EndDate} IS NULL it worked. It's inconvenient, because with an EndDate I don't want to have to set a default value of today + some arbitrary number of days or make it be some crazy, hardcoded date in the future, but at least I have a partial workaround while I explore the nuances of the SQL differences.

I have an iReport that connects to a DB2 database that works great when I hit a version of the database on my PC. But when I try to use the same report against the database on my AIX server, it has a problem with my use of a parameter in the SQL query. The relevant WHERE section is: WHERE "GL"."CATEGORY"='A' AND ("GL_ENTRY"."EFFECTIVE_DATE"<=$P{EndDate} OR $P{EndDate} IS NULL) When I try to preview the report, it prompts me for the EndDate parameter, but then it gives me a SQL -418 error pasted below. If I remove the part of the WHERE statement that has the ${EndDate} parameter, it still prompts me (I haven't removed the parameter from the definition of the report), and the report runs fine, although it's no longer using my parameter input as selection criteria, of course. I've experienced this on iReport 3.5.0 and 3.7.1. I think the back end is on 3.5 version of JasperReport. Any ideas? --Carl Caused by: com.ibm.db2.jcc.am.ro: DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=3.58.82 (Full details of the error copy/pasted into the "Code:" section) Code:WHERE "GL"."CATEGORY"='A' AND ("GL_ENTRY"."EFFECTIVE_DATE"<=$P{EndDate} OR $P{EndDate} IS NULL)Error filling print... Error executing SQL statement for : Asset Accounts net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Asset Accounts     at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:141)     at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:681)     at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:601)     at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1247)     at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877)     at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:826)     at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:59)     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:857)     at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)     at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997) Caused by: com.ibm.db2.jcc.am.ro: DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=3.58.82     at com.ibm.db2.jcc.am.ed.a(ed.java:676)     at com.ibm.db2.jcc.am.ed.a(ed.java:60)     at com.ibm.db2.jcc.am.ed.a(ed.java:127)     at com.ibm.db2.jcc.am.tm.c(tm.java:2523)     at com.ibm.db2.jcc.am.tm.d(tm.java:2511)     at com.ibm.db2.jcc.am.tm.a(tm.java:1991)     at com.ibm.db2.jcc.t4.fb.g(fb.java:140)     at com.ibm.db2.jcc.t4.fb.a(fb.java:40)     at com.ibm.db2.jcc.t4.t.a(t.java:32)     at com.ibm.db2.jcc.t4.ub.i(ub.java:135)     at com.ibm.db2.jcc.am.tm.fb(tm.java:1962)     at com.ibm.db2.jcc.am.um.nc(um.java:2989)     at com.ibm.db2.jcc.am.um.b(um.java:3760)     at com.ibm.db2.jcc.am.um.Zb(um.java:673)     at com.ibm.db2.jcc.am.um.executeQuery(um.java:647)     at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:135)     ... 11 more Print not filled. Try to use an EmptyDataSource...