SQL -418 when I try to use a $P{EndDate} in Query

[cbarlow3]

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...

[cbarlow3]

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. 

[hangman01]

I have never worked with that type of database so I can't say for certain that this will work but I would try something like this:

WHERE
   "GL"."CATEGORY"='A' AND
  "GL_ENTRY"."EFFECTIVE_DATE"<=nvl($P{EndDate} ,"GL_ENTRY"."EFFECTIVE_DATE")

This structure works on Oracle Databases to allow one report to do both for one individual or a large group of individuals.

 

[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')