Error when running report
I have created the query report below. (SDP 8026)
SELECT wo.WORKORDERID "Request ID",longtodate(wo.CREATEDTIME) "Created Time",aau.FIRST_NAME "Requester",
awo.STATUSNAME "Status",awo.URGENCYNAME "Urgency",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",
wo.TITLE "Subject",wotodesc.FULLDESCRIPTION "Description",longtodate(won.NOTESDATE) "Note Date",
won.NOTESTEXT "Note",tsd.Title "Task Title",longtodate(tsd.SCHEDULEDSTARTTIME) "Scheduled Start",longtodate(tsd.SCHEDULEDENDTIME) "Scheduled End",longtodate(tsd.ACTUALSTARTTIME) "Actual Start",longtodate(tsd.ACTUALENDTIME) "Actual End"
FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN Arc_WorkOrder_Notes won ON wo.WORKORDERID=won.WORKORDERID
Left Join Arc_WorkOrder awo ON wo.WORKORDERID=awo.WORKORDERID
LEFT JOIN workordertotaskdetails cdtd ON cdtd.workorderid=wo.workorderid
LEFT JOIN taskdetails tsd ON cdtd.taskid=tsd.taskid
WHERE (qd.QUEUENAME = N'SAP ABAP-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP Accounting-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP BASIS-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP BW-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP FI-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP HR-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP OPS-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP PORTAL-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP SC-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP Security-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP Supply Chain' COLLATE SQL_Latin1_General_CP1_CI_AS)
AND (awo.STATUSNAME != N'Cancelled' COLLATE SQL_Latin1_General_CP1_CS_AS)
AND (awo.STATUSNAME != N'Closed' COLLATE SQL_Latin1_General_CP1_CS_AS)
AND (awo.STATUSNAME != N'Resolved' COLLATE SQL_Latin1_General_CP1_CS_AS)
OR (awo.STATUSNAME IS NULL) AND wo.ISPARENT=1 ORDER BY 6, wo.CREATEDTIME
This runs fine when copied to SQL Management Studio, but I get this error when trying to run in SDP:
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.substring(Unknown Source)
at com.adventnet.servicedesk.reports.utils.ReportUtil.parseDateColumns(ReportUtil.java:2351)
at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7531)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.runSQLQueryReport(ReportRequestHandler.java:1595)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.handleRequest(ReportRequestHandler.java:1890)
at com.adventnet.servicedesk.reports.action.CustomReportHandler.handleRequest(CustomReportHandler.java:176)
at com.adventnet.servicedesk.reports.action.ReportHandler.execute(ReportHandler.java:59)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.adventnet.servicedesk.filter.EncodingFilter.doFilter(EncodingFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:350)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.adventnet.servicedesk.authentication.NTLMV2CredentialAssociationFilter.doFilter(NTLMV2CredentialAssociationFilter.java:34)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.adventnet.servicedesk.authentication.NTLMV2Filter.doFilter(NTLMV2Filter.java:126)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:66)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:158)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at com.adventnet.authentication.CredentialAssociation.invoke(CredentialAssociation.java:75)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at com.adventnet.client.util.LoginParameters.invoke(LoginParameters.java:53)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at com.adventnet.servicedesk.tools.AuthenticateNtlm.invoke(AuthenticateNtlm.java:203)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:444)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Unknown Source)
I've seen this happen a few times. Can you tell me what I should be looking for to fix it?
Thanks,
Sheila
SELECT wo.WORKORDERID "Request ID",longtodate(wo.CREATEDTIME) "Created Time",aau.FIRST_NAME "Requester",
awo.STATUSNAME "Status",awo.URGENCYNAME "Urgency",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",
wo.TITLE "Subject",wotodesc.FULLDESCRIPTION "Description",longtodate(won.NOTESDATE) "Note Date",
won.NOTESTEXT "Note",tsd.Title "Task Title",longtodate(tsd.SCHEDULEDSTARTTIME) "Scheduled Start",longtodate(tsd.SCHEDULEDENDTIME) "Scheduled End",longtodate(tsd.ACTUALSTARTTIME) "Actual Start",longtodate(tsd.ACTUALENDTIME) "Actual End"
FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN Arc_WorkOrder_Notes won ON wo.WORKORDERID=won.WORKORDERID
Left Join Arc_WorkOrder awo ON wo.WORKORDERID=awo.WORKORDERID
LEFT JOIN workordertotaskdetails cdtd ON cdtd.workorderid=wo.workorderid
LEFT JOIN taskdetails tsd ON cdtd.taskid=tsd.taskid
WHERE (qd.QUEUENAME = N'SAP ABAP-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP Accounting-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP BASIS-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP BW-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP FI-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP HR-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP OPS-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP PORTAL-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP SC-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP Security-US' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'SAP Supply Chain' COLLATE SQL_Latin1_General_CP1_CI_AS)
AND (awo.STATUSNAME != N'Cancelled' COLLATE SQL_Latin1_General_CP1_CS_AS)
AND (awo.STATUSNAME != N'Closed' COLLATE SQL_Latin1_General_CP1_CS_AS)
AND (awo.STATUSNAME != N'Resolved' COLLATE SQL_Latin1_General_CP1_CS_AS)
OR (awo.STATUSNAME IS NULL) AND wo.ISPARENT=1 ORDER BY 6, wo.CREATEDTIME
This runs fine when copied to SQL Management Studio, but I get this error when trying to run in SDP:
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.substring(Unknown Source)
at com.adventnet.servicedesk.reports.utils.ReportUtil.parseDateColumns(ReportUtil.java:2351)
at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7531)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.runSQLQueryReport(ReportRequestHandler.java:1595)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.handleRequest(ReportRequestHandler.java:1890)
at com.adventnet.servicedesk.reports.action.CustomReportHandler.handleRequest(CustomReportHandler.java:176)
at com.adventnet.servicedesk.reports.action.ReportHandler.execute(ReportHandler.java:59)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.adventnet.servicedesk.filter.EncodingFilter.doFilter(EncodingFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:350)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.adventnet.servicedesk.authentication.NTLMV2CredentialAssociationFilter.doFilter(NTLMV2CredentialAssociationFilter.java:34)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.adventnet.servicedesk.authentication.NTLMV2Filter.doFilter(NTLMV2Filter.java:126)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:66)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:158)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at com.adventnet.authentication.CredentialAssociation.invoke(CredentialAssociation.java:75)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at com.adventnet.client.util.LoginParameters.invoke(LoginParameters.java:53)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at com.adventnet.servicedesk.tools.AuthenticateNtlm.invoke(AuthenticateNtlm.java:203)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:444)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Unknown Source)
I've seen this happen a few times. Can you tell me what I should be looking for to fix it?
Thanks,
Sheila