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

Hi,


 


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.



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

Here is my Code:



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;
}

Regards,
Muhammad Waqas
Software Engineer
Avanza Solutions (Pvt.) Ltd.
Office # 14-B, Fakhri Trade Centre SR 6/10, Shahrah-e-Liaquat New Challi, Karachi-74200, Pakistan.
M   + 92 345 8928822
E    [email protected]
W   www.avanzasolutions.com

Comments

  • VirgilVirgil Administrator EM admin

    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:


      var query=' ';


    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:



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


    //later in your query use this to force Integer
    dateyear.valueOf()

    Virgil Dodson
    OpenText Analytics Developer Community
    https://forums.opentext.com/forums/categories/analytics

  • edited May 24, 2017

    After modifying my query I got another exception.


     


    Code:



    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;
    }

    Error:



    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

    Regards,
    Muhammad Waqas
    Software Engineer
    Avanza Solutions (Pvt.) Ltd.
    Office # 14-B, Fakhri Trade Centre SR 6/10, Shahrah-e-Liaquat New Challi, Karachi-74200, Pakistan.
    M   + 92 345 8928822
    E    [email protected]
    W   www.avanzasolutions.com

  • VirgilVirgil Administrator EM admin
    edited May 24, 2017

    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.



    var mydate1 = new Date(params["txtFromDate"]);
    var dateyear1 = (mydate1.getYear() +1900);
    dateyear1;

    Try your parameters one at a time and once you get that working, plugging it into the huge query should be easier.


     


    You probably don't even need the Data Source and Data Set for this test.


    Virgil Dodson
    OpenText Analytics Developer Community
    https://forums.opentext.com/forums/categories/analytics

Sign In or Register to comment.