getYear(), getYear()+1900, getFullYear().

Muhammad Waqas
edited February 11, 2022 in Analytics #1
<p>Hi,</p>
<p> </p>
<p>When I return date from getYear(); It returns nothing. When I return getFullYear(); It returns error that getFullYear() is not defined. And when I return getYear() + 1900 it gives following error.</p>
<pre class="_prettyXprint _lang-html">
org.eclipse.birt.report.engine.api.EngineException: Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1245)
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1224)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:96)
at org.eclipse.birt.report.engine.executor.DataItemExecutor.execute(DataItemExecutor.java:75)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46)
at org.eclipse.birt.report.engine.internal.executor.emitter.ReportItemEmitterExecutor.execute(ReportItemEmitterExecutor.java:46)
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65)
at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:140)
at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.resumeLayout(HTMLInlineStackingLM.java:111)
at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.layoutNodes(HTMLInlineStackingLM.java:160)
at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:140)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:70)
at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)
at org.eclipse.birt.report.engine.layout.html.HTMLRepeatHeaderLM.layoutChildren(HTMLRepeatHeaderLM.java:46)
at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:140)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:70)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:100)
at org.eclipse.birt.report.engine.presentation.ReportDocumentBuilder.build(ReportDocumentBuilder.java:258)
at org.eclipse.birt.report.engine.api.impl.RunTask.doRun(RunTask.java:273)
at org.eclipse.birt.report.engine.api.impl.RunTask.run(RunTask.java:89)
at org.eclipse.birt.report.service.ReportEngineService.runReport(ReportEngineService.java:1325)
at org.eclipse.birt.report.service.BirtViewerReportService.runReport(BirtViewerReportService.java:159)
at org.eclipse.birt.report.service.actionhandler.BirtRunReportActionHandler.__execute(BirtRunReportActionHandler.java:81)
at org.eclipse.birt.report.service.actionhandler.BirtChangeParameterActionHandler.runReport(BirtChangeParameterActionHandler.java:58)
at org.eclipse.birt.report.service.actionhandler.AbstractChangeParameterActionHandler.__execute(AbstractChangeParameterActionHandler.java:52)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143)
at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleChangeParameter(BirtDocumentProcessor.java:100)
at sun.reflect.GeneratedMethodAccessor112.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112)
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66)
at sun.reflect.GeneratedMethodAccessor111.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.eclipse.birt.report.filter.ViewerFilter.doFilter(ViewerFilter.java:68)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1533)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1489)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:586)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:978)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:607)
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1251)
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:233)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:178)
at org.eclipse.birt.report.engine.data.dte.QueryResultSet.(QueryResultSet.java:98)
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:168)
at org.eclipse.birt.report.engine.data.dte.DataGenerationEngine.doExecuteQuery(DataGenerationEngine.java:83)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:285)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1947)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80)
... 76 more
Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:481)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doExecuteQuery(OdaQuery.java:480)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.executeQuery(OdaQuery.java:444)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:575)
... 87 more
Caused by: java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.isDataInResultSet(TdsCore.java:838)
at net.sourceforge.jtds.jdbc.JtdsResultSet.(JtdsResultSet.java:149)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:511)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1029)
at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:477)
... 90 more
org.eclipse.birt.report.engine.api.EngineException: Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1245)
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1224)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:96)
at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:62)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46)
at org.eclipse.birt.report.engine.internal.executor.emitter.ReportItemEmitterExecutor.execute(ReportItemEmitterExecutor.java:46)
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:100)
at org.eclipse.birt.report.engine.presentation.ReportDocumentBuilder.build(ReportDocumentBuilder.java:258)
at org.eclipse.birt.report.engine.api.impl.RunTask.doRun(RunTask.java:273)
at org.eclipse.birt.report.engine.api.impl.RunTask.run(RunTask.java:89)
at org.eclipse.birt.report.service.ReportEngineService.runReport(ReportEngineService.java:1325)
at org.eclipse.birt.report.service.BirtViewerReportService.runReport(BirtViewerReportService.java:159)
at org.eclipse.birt.report.service.actionhandler.BirtRunReportActionHandler.__execute(BirtRunReportActionHandler.java:81)
at org.eclipse.birt.report.service.actionhandler.BirtChangeParameterActionHandler.runReport(BirtChangeParameterActionHandler.java:58)
at org.eclipse.birt.report.service.actionhandler.AbstractChangeParameterActionHandler.__execute(AbstractChangeParameterActionHandler.java:52)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143)
at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleChangeParameter(BirtDocumentProcessor.java:100)
at sun.reflect.GeneratedMethodAccessor112.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112)
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66)
at sun.reflect.GeneratedMethodAccessor111.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.eclipse.birt.report.filter.ViewerFilter.doFilter(ViewerFilter.java:68)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1533)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1489)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:586)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:978)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:607)
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1251)
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:233)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:178)
at org.eclipse.birt.report.engine.data.dte.QueryResultSet.(QueryResultSet.java:98)
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:168)
at org.eclipse.birt.report.engine.data.dte.DataGenerationEngine.doExecuteQuery(DataGenerationEngine.java:83)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:285)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1947)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80)
... 65 more
Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:481)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doExecuteQuery(OdaQuery.java:480)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.executeQuery(OdaQuery.java:444)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:575)
... 76 more
Caused by: java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.isDataInResultSet(TdsCore.java:838)
at net.sourceforge.jtds.jdbc.JtdsResultSet.(JtdsResultSet.java:149)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:511)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1029)
at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:477)
... 79 more
org.eclipse.birt.report.engine.api.EngineException: An exception occurred during processing. Please see the following message for details:
exception.error
Error happens when create hierarchy:Group.NewTabularHierarchy.
Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at org.eclipse.birt.report.engine.executor.ExecutorManager$ExecutorContext.executeQuery(ExecutorManager.java:457)
at org.eclipse.birt.report.item.crosstab.core.re.executor.BaseCrosstabExecutor.executeQuery(BaseCrosstabExecutor.java:122)
at org.eclipse.birt.report.item.crosstab.core.re.executor.CrosstabReportItemExecutor.execute(CrosstabReportItemExecutor.java:102)
at org.eclipse.birt.report.engine.executor.ExtendedItemExecutor.execute(ExtendedItemExecutor.java:62)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46)
at org.eclipse.birt.report.engine.internal.executor.emitter.ReportItemEmitterExecutor.execute(ReportItemEmitterExecutor.java:46)
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:100)
at org.eclipse.birt.report.engine.presentation.ReportDocumentBuilder.build(ReportDocumentBuilder.java:258)
at org.eclipse.birt.report.engine.api.impl.RunTask.doRun(RunTask.java:273)
at org.eclipse.birt.report.engine.api.impl.RunTask.run(RunTask.java:89)
at org.eclipse.birt.report.service.ReportEngineService.runReport(ReportEngineService.java:1325)
at org.eclipse.birt.report.service.BirtViewerReportService.runReport(BirtViewerReportService.java:159)
at org.eclipse.birt.report.service.actionhandler.BirtRunReportActionHandler.__execute(BirtRunReportActionHandler.java:81)
at org.eclipse.birt.report.service.actionhandler.BirtChangeParameterActionHandler.runReport(BirtChangeParameterActionHandler.java:58)
at org.eclipse.birt.report.service.actionhandler.AbstractChangeParameterActionHandler.__execute(AbstractChangeParameterActionHandler.java:52)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143)
at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleChangeParameter(BirtDocumentProcessor.java:100)
at sun.reflect.GeneratedMethodAccessor112.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112)
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66)
at sun.reflect.GeneratedMethodAccessor111.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.eclipse.birt.report.filter.ViewerFilter.doFilter(ViewerFilter.java:68)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1533)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1489)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: An exception occurred during processing. Please see the following message for details:
exception.error
Error happens when create hierarchy:Group.NewTabularHierarchy.
Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:671)
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteCube(DteDataEngine.java:233)
at org.eclipse.birt.report.engine.data.dte.DataGenerationEngine.doExecuteCube(DataGenerationEngine.java:96)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:290)
at org.eclipse.birt.report.engine.executor.ExecutorManager$ExecutorContext.executeQuery(ExecutorManager.java:447)
... 67 more
Caused by: org.eclipse.birt.data.engine.core.DataException: exception.error
Error happens when create hierarchy:Group.NewTabularHierarchy.
Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.materializeCube(DataRequestSessionImpl.java:802)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:660)
... 71 more
Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: Error happens when create hierarchy:Group.NewTabularHierarchy.
Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.populateDimension(DataRequestSessionImpl.java:1501)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.populateDimensions(DataRequestSessionImpl.java:1359)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.createCube(DataRequestSessionImpl.java:853)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.materializeCube(DataRequestSessionImpl.java:794)
... 72 more
Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at org.eclipse.birt.report.data.adapter.impl.DataSetIterator.executeQuery(DataSetIterator.java:92)
at org.eclipse.birt.report.data.adapter.impl.DataSetIterator.(DataSetIterator.java:125)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.populateDimension(DataRequestSessionImpl.java:1486)
... 75 more
Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:586)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:978)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:607)
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1251)
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:233)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:178)
at org.eclipse.birt.report.data.adapter.impl.DataSetIterator.executeQuery(DataSetIterator.java:88)
... 77 more
Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Conversion failed when converting the varchar value '114 +1900' to data type int.
;
java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:481)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doExecuteQuery(OdaQuery.java:480)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.executeQuery(OdaQuery.java:444)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:575)
... 83 more
Caused by: java.sql.SQLException: Conversion failed when converting the varchar value '114 +1900' to data type int.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.isDataInResultSet(TdsCore.java:838)
at net.sourceforge.jtds.jdbc.JtdsResultSet.(JtdsResultSet.java:149)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:511)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1029)
at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:477)
... 86 more
</pre>
<p><strong>Here is my Code:</strong></p>
<pre class="_prettyXprint _lang-js">
var query='';

if(params["cmbState"].value[0] != "ALL") {
query= " AND WF_GLOBAL_STATE.STATE_CODE IN('"+params["cmbState"].value.join("','")+"')";
}

if(params["cmbType"].value !== "ALL" ) {
query= query+" AND META_ENTITY.META_ENT_ID ='"+params["cmbType"].value+"'";
}

if(params["cmbUsers"].value[0] != "ALL" ) {
query= query+" AND SEC_USER.LOGIN_ID IN ('"+params["cmbUsers"].value.join("','")+"')";
}

if(params["cmbOrgUnitType"].value !== "ALL" ){
query= query+" AND ORG_UNIT_TYPE.UNIT_TYPE_ID ='"+params["cmbOrgUnitType"].value+"'";
}

if(params["cmbOrgType"].value !== "ALL" ){
query= query+" AND ORG_UNIT.ORG_UNIT_ID ='"+params["cmbOrgType"].value+"'";
}

if(params["cmbCategory"].value !== "ALL" ){
query= query+" AND VALUE_TREE_NODE.TREE_NODE_ID ='"+params["cmbCategory"].value+"'";
}


if (query=='') {
this.queryText = " SELECT WF_GLOBAL_STATE.STATE_NAME_PRM, 'Branch' AS OrgName, 'Channel' AS Channel, 'User' AS userId, 'Start Date' AS StartTime, 'End Date' AS EndTime,"+
" COMPLAINT.CURRENT_STATE, COMPLAINT.DOC_PRIORITY, COMPLAINT.PROCESS_START_TIME, COMPLAINT.DOC_MEDIUM, "+
" COMPLAINT.CURRENT_ORG_UNIT, COMPLAINT.COMP_TICKET_NUM, COMPLAINT.CUST_RELATION_NUM, "+
" COMPLAINT.STATE_ENTRY_TIME, COMPLAINT.UPDATED_ON, "+
" ORG_UNIT.ORG_NAME_PRM, '' AS FirstLevel, '' AS SecondLevel, COMPLAINT.COMPLAINT_TYPE, "+
" COMPLAINT.PRODUCT_CATEGORY_ID, COMPLAINT.CURRENT_ACTOR, SEC_USER.LOGIN_ID, SEC_USER.FULL_NAME, "+
" COMPLAINT.CREATED_BY, META_ENTITY.META_ENT_NAME_PRM, COMPLAINT.NOTES, VALUE_TREE_NODE.TREE_NODE_NAME_PRM,"+
" WORKFLOW_LOG.LOG_ID, WORKFLOW_LOG.CREATED_ON, DATEPART(MM,COMPLAINT.CREATED_ON) AS Monthly, DATEPART(yyyy,COMPLAINT.CREATED_ON) AS Yearly"+
" FROM COMPLAINT INNER JOIN"+
" WF_GLOBAL_STATE ON COMPLAINT.CURRENT_STATE = WF_GLOBAL_STATE.STATE_CODE INNER JOIN"+
" SEC_USER ON COMPLAINT.CREATED_BY = SEC_USER.LOGIN_ID INNER JOIN"+
" META_ENTITY ON COMPLAINT.COMPLAINT_TYPE = META_ENTITY.META_ENT_ID LEFT OUTER JOIN"+
" ORG_UNIT ON COMPLAINT.INVOLVE = ORG_UNIT.ORG_UNIT_ID INNER JOIN"+
" WORKFLOW_LOG ON COMPLAINT.COMP_TICKET_NUM = WORKFLOW_LOG.REF_DOC_NUM LEFT OUTER JOIN"+
" VALUE_TREE_NODE ON COMPLAINT.PRODUCT_CODE = VALUE_TREE_NODE.OTHER_CODE"+
" LEFT OUTER JOIN ORG_UNIT_TYPE ON ORG_UNIT.UNIT_TYPE_ID = ORG_UNIT_TYPE.UNIT_TYPE_ID"+
" WHERE DATEPART(MM,COMPLAINT.CREATED_ON) = '"+params["txtFromDate"].getMonth()+"' AND DATEPART(yyyy,COMPLAINT.CREATED_ON) = '"+params["txtToDate"].getYear()+" +1900'"
} else {
this.queryText = " SELECT WF_GLOBAL_STATE.STATE_NAME_PRM, 'Branch' AS OrgName, 'Channel' AS Channel, 'User' AS userId, 'Start Date' AS StartTime, 'End Date' AS EndTime,"+
" COMPLAINT.CURRENT_STATE, COMPLAINT.DOC_PRIORITY, COMPLAINT.PROCESS_START_TIME, COMPLAINT.DOC_MEDIUM, "+
" COMPLAINT.CURRENT_ORG_UNIT, COMPLAINT.COMP_TICKET_NUM, COMPLAINT.CUST_RELATION_NUM, "+
" COMPLAINT.STATE_ENTRY_TIME, COMPLAINT.UPDATED_ON, "+
" ORG_UNIT.ORG_NAME_PRM, '' AS FirstLevel, '' AS SecondLevel, COMPLAINT.COMPLAINT_TYPE, "+
" COMPLAINT.PRODUCT_CATEGORY_ID, COMPLAINT.CURRENT_ACTOR, SEC_USER.LOGIN_ID, SEC_USER.FULL_NAME, "+
" COMPLAINT.CREATED_BY, META_ENTITY.META_ENT_NAME_PRM, COMPLAINT.NOTES, VALUE_TREE_NODE.TREE_NODE_NAME_PRM,"+
" WORKFLOW_LOG.LOG_ID, WORKFLOW_LOG.CREATED_ON, DATEPART(MM,COMPLAINT.CREATED_ON) AS Monthly, DATEPART(yyyy,COMPLAINT.CREATED_ON) AS Yearly"+
" FROM COMPLAINT INNER JOIN"+
" WF_GLOBAL_STATE ON COMPLAINT.CURRENT_STATE = WF_GLOBAL_STATE.STATE_CODE INNER JOIN"+
" SEC_USER ON COMPLAINT.CREATED_BY = SEC_USER.LOGIN_ID INNER JOIN"+
" META_ENTITY ON COMPLAINT.COMPLAINT_TYPE = META_ENTITY.META_ENT_ID LEFT OUTER JOIN"+
" ORG_UNIT ON COMPLAINT.INVOLVE = ORG_UNIT.ORG_UNIT_ID INNER JOIN"+
" WORKFLOW_LOG ON COMPLAINT.COMP_TICKET_NUM = WORKFLOW_LOG.REF_DOC_NUM LEFT OUTER JOIN"+
" VALUE_TREE_NODE ON COMPLAINT.PRODUCT_CODE = VALUE_TREE_NODE.OTHER_CODE"+
" LEFT OUTER JOIN ORG_UNIT_TYPE ON ORG_UNIT.UNIT_TYPE_ID = ORG_UNIT_TYPE.UNIT_TYPE_ID"+
" WHERE DATEPART(MM,COMPLAINT.CREATED_ON) = '"+params["txtFromDate"].getMonth()+"' AND DATEPART(yyyy,COMPLAINT.CREATED_ON) = '"+params["txtToDate"].getYear()+" +1900'"+query;
}
</pre>
Warning No formatter is installed for the format ipb

Comments

  • <p>It looks like getYear() is working because I can see 114 in the stack trace.  In your example, since you are immediately putting everything into a string field called "query" defined as:</p>
    <p><span>  var</span><span> query</span><span>=</span><span>' '</span><span>;</span></p>
    <p>the getYear statement is turning into a string before you do the math.  Do your math first, and then use the variable in your query like this:</p>
    <pre class="_prettyXprint">
    var mydate = new Date(params["txtToDate"]);
    var dateyear = (mydate.getYear() +1900);


    //later in your query use this to force Integer
    dateyear.valueOf()
    </pre>
    Warning No formatter is installed for the format ipb
  • Muhammad Waqas
    edited May 24, 2017 #3
    <p>After modifying my query I got another exception.</p>
    <p> </p>
    <p><strong>Code:</strong></p>
    <pre class="_prettyXprint _lang-js">
    var query='';
    var mydate1 = new Date(params["txtFromDate"]);
    var dateyear1 = (mydate1.getYear() +1900);

    var mydate2 = new Date(params["txtToDate"]);
    var dateyear2 = (mydate2.getYear() +1900);

    if(params["cmbState"].value[0] != "ALL") {
    query= " AND WF_GLOBAL_STATE.STATE_CODE IN('"+params["cmbState"].value.join("','")+"')";
    }

    if(params["cmbType"].value !== "ALL" ) {
    query= query+" AND META_ENTITY.META_ENT_ID ='"+params["cmbType"].value+"'";
    }

    if(params["cmbUsers"].value[0] != "ALL" ) {
    query= query+" AND SEC_USER.LOGIN_ID IN ('"+params["cmbUsers"].value.join("','")+"')";
    }

    if(params["cmbOrgUnitType"].value !== "ALL" ){
    query= query+" AND ORG_UNIT_TYPE.UNIT_TYPE_ID ='"+params["cmbOrgUnitType"].value+"'";
    }

    if(params["cmbOrgType"].value !== "ALL" ){
    query= query+" AND ORG_UNIT.ORG_UNIT_ID ='"+params["cmbOrgType"].value+"'";
    }

    if(params["cmbCategory"].value !== "ALL" ){
    query= query+" AND VALUE_TREE_NODE.TREE_NODE_ID ='"+params["cmbCategory"].value+"'";
    }


    if (query=='') {
    this.queryText = " SELECT WF_GLOBAL_STATE.STATE_NAME_PRM, 'Branch' AS OrgName, 'Channel' AS Channel, 'User' AS userId, 'Start Date' AS StartTime, 'End Date' AS EndTime,"+
    " COMPLAINT.CURRENT_STATE, COMPLAINT.DOC_PRIORITY, COMPLAINT.PROCESS_START_TIME, COMPLAINT.DOC_MEDIUM, "+
    " COMPLAINT.CURRENT_ORG_UNIT,COMPLAINT.COMP_TICKET_NUM,COMPLAINT.COMP_TICKET_NUM AS COMP_TICKET_NUM1, COMPLAINT.CUST_RELATION_NUM, "+
    " COMPLAINT.STATE_ENTRY_TIME, COMPLAINT.UPDATED_ON, "+
    " ORG_UNIT.ORG_NAME_PRM, '' AS FirstLevel, '' AS SecondLevel, COMPLAINT.COMPLAINT_TYPE, "+
    " COMPLAINT.PRODUCT_CATEGORY_ID, COMPLAINT.CURRENT_ACTOR, SEC_USER.LOGIN_ID, SEC_USER.FULL_NAME, "+
    " COMPLAINT.CREATED_BY, META_ENTITY.META_ENT_NAME_PRM, COMPLAINT.NOTES, VALUE_TREE_NODE.TREE_NODE_NAME_PRM,"+
    " WORKFLOW_LOG.LOG_ID, WORKFLOW_LOG.CREATED_ON, DATEPART(MM,COMPLAINT.CREATED_ON) AS Monthly, DATEPART(yyyy,COMPLAINT.CREATED_ON) AS Yearly"+
    " FROM COMPLAINT INNER JOIN"+
    " WF_GLOBAL_STATE ON COMPLAINT.CURRENT_STATE = WF_GLOBAL_STATE.STATE_CODE INNER JOIN"+
    " SEC_USER ON COMPLAINT.CREATED_BY = SEC_USER.LOGIN_ID INNER JOIN"+
    " META_ENTITY ON COMPLAINT.COMPLAINT_TYPE = META_ENTITY.META_ENT_ID LEFT OUTER JOIN"+
    " ORG_UNIT ON COMPLAINT.INVOLVE = ORG_UNIT.ORG_UNIT_ID INNER JOIN"+
    " WORKFLOW_LOG ON COMPLAINT.COMP_TICKET_NUM = WORKFLOW_LOG.REF_DOC_NUM LEFT OUTER JOIN"+
    " VALUE_TREE_NODE ON COMPLAINT.PRODUCT_CODE = VALUE_TREE_NODE.OTHER_CODE"+
    " LEFT OUTER JOIN ORG_UNIT_TYPE ON ORG_UNIT.UNIT_TYPE_ID = ORG_UNIT_TYPE.UNIT_TYPE_ID"+
    " WHERE DATEPART(MM,COMPLAINT.CREATED_ON) = '"+params["txtFromDate"].getMonth()+"' AND DATEPART(yyyy,COMPLAINT.CREATED_ON) = '"+dateyear1.valueOf()+"'"+
    " AND DATEPART(MM,COMPLAINT.CREATED_ON) = '"+params["txtToDate"].getMonth()+"' AND DATEPART(yyyy,COMPLAINT.CREATED_ON) = '"+dateyear2.valueOf()+"'"

    } else {
    this.queryText = " SELECT WF_GLOBAL_STATE.STATE_NAME_PRM, 'Branch' AS OrgName, 'Channel' AS Channel, 'User' AS userId, 'Start Date' AS StartTime, 'End Date' AS EndTime,"+
    " COMPLAINT.CURRENT_STATE, COMPLAINT.DOC_PRIORITY, COMPLAINT.PROCESS_START_TIME, COMPLAINT.DOC_MEDIUM, "+
    " COMPLAINT.CURRENT_ORG_UNIT,COMPLAINT.COMP_TICKET_NUM,COMPLAINT.COMP_TICKET_NUM AS COMP_TICKET_NUM1, COMPLAINT.CUST_RELATION_NUM, "+
    " COMPLAINT.STATE_ENTRY_TIME, COMPLAINT.UPDATED_ON, "+
    " ORG_UNIT.ORG_NAME_PRM, '' AS FirstLevel, '' AS SecondLevel, COMPLAINT.COMPLAINT_TYPE, "+
    " COMPLAINT.PRODUCT_CATEGORY_ID, COMPLAINT.CURRENT_ACTOR, SEC_USER.LOGIN_ID, SEC_USER.FULL_NAME, "+
    " COMPLAINT.CREATED_BY, META_ENTITY.META_ENT_NAME_PRM, COMPLAINT.NOTES, VALUE_TREE_NODE.TREE_NODE_NAME_PRM,"+
    " WORKFLOW_LOG.LOG_ID, WORKFLOW_LOG.CREATED_ON, DATEPART(MM,COMPLAINT.CREATED_ON) AS Monthly, DATEPART(yyyy,COMPLAINT.CREATED_ON) AS Yearly"+
    " FROM COMPLAINT INNER JOIN"+
    " WF_GLOBAL_STATE ON COMPLAINT.CURRENT_STATE = WF_GLOBAL_STATE.STATE_CODE INNER JOIN"+
    " SEC_USER ON COMPLAINT.CREATED_BY = SEC_USER.LOGIN_ID INNER JOIN"+
    " META_ENTITY ON COMPLAINT.COMPLAINT_TYPE = META_ENTITY.META_ENT_ID LEFT OUTER JOIN"+
    " ORG_UNIT ON COMPLAINT.INVOLVE = ORG_UNIT.ORG_UNIT_ID INNER JOIN"+
    " WORKFLOW_LOG ON COMPLAINT.COMP_TICKET_NUM = WORKFLOW_LOG.REF_DOC_NUM LEFT OUTER JOIN"+
    " VALUE_TREE_NODE ON COMPLAINT.PRODUCT_CODE = VALUE_TREE_NODE.OTHER_CODE"+
    " LEFT OUTER JOIN ORG_UNIT_TYPE ON ORG_UNIT.UNIT_TYPE_ID = ORG_UNIT_TYPE.UNIT_TYPE_ID"+
    " WHERE DATEPART(MM,COMPLAINT.CREATED_ON) = '"+params["txtFromDate"].getMonth()+"' AND DATEPART(yyyy,COMPLAINT.CREATED_ON) = '"+dateyear1.valueOf()+"'"+
    " AND DATEPART(MM,COMPLAINT.CREATED_ON) = '"+params["txtToDate"].getMonth()+"' AND DATEPART(yyyy,COMPLAINT.CREATED_ON) = '"+dateyear2.valueOf()+"'"+query;
    }
    </pre>
    <p><strong>Error:</strong></p>
    <pre class="_prettyXprint _lang-html">
    org.eclipse.birt.report.engine.api.EngineException: Cannot execute the statement.
    org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
    SQL error #1:Conversion failed when converting the varchar value 'NaN' to data type int.
    ;
    java.sql.SQLException: Conversion failed when converting the varchar value 'NaN' to data type int.
    at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1245)
    at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1224)
    at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:96)
    at org.eclipse.birt.report.engine.executor.DataItemExecutor.execute(DataItemExecutor.java:75)
    at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46)
    at org.eclipse.birt.report.engine.internal.executor.emitter.ReportItemEmitterExecutor.execute(ReportItemEmitterExecutor.java:46)
    at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43)
    at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65)
    at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)
    at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:140)
    at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.resumeLayout(HTMLInlineStackingLM.java:111)
    at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.layoutNodes(HTMLInlineStackingLM.java:160)
    at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)
    at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:140)
    at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:70)
    at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)
    at org.eclipse.birt.report.engine.layout.html.HTMLRepeatHeaderLM.layoutChildren(HTMLRepeatHeaderLM.java:46)
    at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:140)
    at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:70)
    at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92)
    at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:100)
    at org.eclipse.birt.report.engine.presentation.ReportDocumentBuilder.build(ReportDocumentBuilder.java:258)
    at org.eclipse.birt.report.engine.api.impl.RunTask.doRun(RunTask.java:273)
    at org.eclipse.birt.report.engine.api.impl.RunTask.run(RunTask.java:89)
    at org.eclipse.birt.report.service.ReportEngineService.runReport(ReportEngineService.java:1325)
    at org.eclipse.birt.report.service.BirtViewerReportService.runReport(BirtViewerReportService.java:159)
    at org.eclipse.birt.report.service.actionhandler.BirtRunReportActionHandler.__execute(BirtRunReportActionHandler.java:81)
    at org.eclipse.birt.report.service.actionhandler.BirtChangeParameterActionHandler.runReport(BirtChangeParameterActionHandler.java:58)
    at org.eclipse.birt.report.service.actionhandler.AbstractChangeParameterActionHandler.__execute(AbstractChangeParameterActionHandler.java:52)
    at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)
    at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47)
    at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143)
    at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleChangeParameter(BirtDocumentProcessor.java:100)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112)
    at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
    at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
    at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
    at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
    at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
    at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
    at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
    at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
    at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
    at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.eclipse.birt.report.filter.ViewerFilter.doFilter(ViewerFilter.java:68)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1533)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1489)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Unknown Source)
    Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot execute the statement.
    org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
    SQL error #1:Conversion failed when converting the varchar value 'NaN' to data type int.
    ;
    java.sql.SQLException: Conversion failed when converting the varchar value 'NaN' to data type int.
    at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)
    at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)
    at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)
    at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:586)
    at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:978)
    at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:607)
    at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1251)
    at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:233)
    at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:178)
    at org.eclipse.birt.report.engine.data.dte.QueryResultSet.(QueryResultSet.java:98)
    at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:168)
    at org.eclipse.birt.report.engine.data.dte.DataGenerationEngine.doExecuteQuery(DataGenerationEngine.java:83)
    at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:285)
    at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1947)
    at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80)
    ... 78 more
    Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
    SQL error #1:Conversion failed when converting the varchar value 'NaN' to data type int.
    ;
    java.sql.SQLException: Conversion failed when converting the varchar value 'NaN' to data type int.
    at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:481)
    at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doExecuteQuery(OdaQuery.java:480)
    at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.executeQuery(OdaQuery.java:444)
    at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:575)
    ... 89 more
    Caused by: java.sql.SQLException: Conversion failed when converting the varchar value 'NaN' to data type int.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
    at net.sourceforge.jtds.jdbc.TdsCore.isDataInResultSet(TdsCore.java:838)
    at net.sourceforge.jtds.jdbc.JtdsResultSet.(JtdsResultSet.java:149)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:511)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1029)
    at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:477)
    ... 92 more
    </pre>
    Warning No formatter is installed for the format ipb
  • Virgil Dodson
    Virgil Dodson E admin
    edited May 24, 2017 #4
    <p>That's telling you that value in either dateyear1 or dateyear2 is not a number.  I would start with a much simplier example one step at a time if I were you.  Create a new blank report, copy the data source and data set and the 2 date parameters... Then put a single data field on the report and paste this script into it.</p>
    <pre class="_prettyXprint _lang-">
    var mydate1 = new Date(params["txtFromDate"]);
    var dateyear1 = (mydate1.getYear() +1900);
    dateyear1;
    </pre>
    <p>Try your parameters one at a time and once you get that working, plugging it into the huge query should be easier.</p>
    <p> </p>
    <p>You probably don't even need the Data Source and Data Set for this test.</p>
    Warning No formatter is installed for the format ipb