<p>I am using Maximo 7.5 and BIRT 3.7.1. I created a scripted data source with Maximo. When I eliminate the case statement, my report displays correctly, but when I include the CASE, I get the following error: [3/11/15</p>
<p> </p>
<p>10:16:26:618 PDT] 0000005e SystemOut O 11 Mar 2015 10:16:26:618 [ERROR] [MXServer] [] DataSet [dataSet] fetch failed: </p>
<div>com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'CASE'.</div>
<div>at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)</div>
<div>at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)</div>
<div>at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)</div>
<div>...</div>
<div> </div>
<div>I have tried using and IF statement and got a similar error. My open script is as follows: </div>
<div> </div>
<div>
<div>maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());</div>
<div>maximoDataSet.open();</div>
<div> </div>
<div>var sqlText = new String();</div>
<div> </div>
<div>sqlText = "select wo.worktype, wt.wtypedesc, wo.wonum, wo.description, wo.status, wo.statusdate, wo.lead, "</div>
<div>+ "<span> </span>wo.actfinish, wo.targcompdate, wo.actfinish, wo.reportdate, (getdate()-wo.reportdate) as days, "</div>
<div>+ "<span> </span>(CASE "</div>
<div>+ "<span> </span> <span> </span>WHEN (month(wo.actfinish) = MONTH(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)) "</div>
<div>+ "<span> </span>and YEAR(wo.actfinish) = YEAR(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)) ) "</div>
<div>+ "<span> </span> <span> </span>THEN 'CLOSE' "</div>
<div>+ "<span> </span> <span> </span>ELSE 'BACKLOG' "</div>
<div>+ "<span> </span> <span> </span>END) as blstatus "</div>
<div>+ " from workorder wo "</div>
<div>+ " left outer join worktype wt on wt.worktype = wo.worktype "</div>
<div>+ " where " + params["where"]</div>
<div>+ "<span> </span>and istask = 0 "</div>
<div>+ "<span> </span>and wo.status != 'CAN' "</div>
<div>+ "<span> </span>and (wo.actfinish >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) or wo.actfinish IS NULL) "</div>
<div>+ "<span> </span>and wo.reportdate < DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) "</div>
<div>;</div>
<div> </div>
<div>maximoDataSet.setQuery(sqlText);</div>
<div> </div>
<div>What can I do to replace the CASE statement or use the case statement in BIRT? The query works flawlessly in SQL.</div>
</div>
<p> </p>
