Query Report produces error
I am trying to produce a report using a custom query and am getting an error reported through the MESD query editor. This query runs fine in a query analyser. I am using MYSQL and MESD Plus - MSP v8.1 Build 8106 and I have several other customer queries that run without issue.
The query is:
select
wot.workorderid "Request ID",
name "Site",
ifnull(first_name, 'Unassigned') "Analyst",
case pd.PRIORITYNAME when '1. Critical' then 'Critical'
when '2. High' then 'High'
when '3. Medium' then 'Medium'
when '4. Low' then 'Low'
when 'Change' then 'Change'
End "Priority",
from_unixtime(wo.createdtime/1000,'%d-%m-%Y %H:%i:%s') "Open Date",
(case when wo.completedtime!=0 then from_unixtime(wo.completedtime/1000,'%d-%m-%Y %H:%i:%s') else 'Not Yet Completed' end) "Completed Date",
from_unixtime(wo.duebytime/1000,'%d-%m-%Y %H:%i:%s') "SLA Date",
DATEDIFF((CASE WHEN wo.completedtime > 0 THEN FROM_UNIXTIME(wo.completedtime/1000) ELSE curdate() END),FROM_UNIXTIME(wo.duebytime/1000)) "Days Over Target SLA"
from workorder_threaded wot
inner join workorder wo on wo.workorderid = wot.workorderid
left join workorderstates wos on wo.workorderid= wos.workorderid
LEFT JOIN sitedefinition sd on wo.siteid = sd.siteid
left join sdorganization sdo on sdo.org_id = sd.siteid
left join sduser sdu on sdu.userid = wos.ownerid
LEFT JOIN aaauser ti on sdu.userid=ti.user_id
left JOIN prioritydefinition pd ON wos.priorityid = pd.priorityid
where wo.duebytime >= (UNIX_TIMESTAMP(date_format(curdate() - interval 8 day,'%Y-%m-%d'))*1000)
AND wo.duebytime <= (UNIX_TIMESTAMP(curdate()-interval 1 day)*1000)
and wot.workorderid = wot.thd_woid
and wos.isoverdue = 1
order by wot.workorderid
The error I am getting is:
java.lang.Exception: Only select queries can be executed - 'select
wot.workorderid "Request ID",
name "Site",
ifnull(first_name, 'Unassigned') "Analyst",
case pd.PRIORITYNAME when '1. Critical' then 'Critical'
when '2. High' then 'High'
when '3. Medium' then 'Medium'
when '4. Low' then 'Low'
when 'Change' then 'Change'
End "Priority",
from_unixtime(wo.createdtime/1000,'%d-%m-%Y %H:%i:%s') "Open Date",
(case when wo.completedtime!=0 then from_unixtime(wo.completedtime/1000,'%d-%m-%Y %H:%i:%s') else 'Not Yet Completed' end) "Completed Date",
from_unixtime(wo.duebytime/1000,'%d-%m-%Y %H:%i:%s') "SLA Date",
DATEDIFF((CASE WHEN wo.completedtime > 0 THEN FROM_UNIXTIME(wo.completedtime/1000) ELSE curdate() END),FROM_UNIXTIME(wo.duebytime/1000)) "Days Over Target SLA"
from workorder_threaded wot
inner join workorder wo on wo.workorderid = wot.workorderid
left join workorderstates wos on wo.workorderid= wos.workorderid
LEFT JOIN sitedefinition sd on wo.siteid = sd.siteid
left join sdorganization sdo on sdo.org_id = sd.siteid
left join sduser sdu on sdu.userid = wos.ownerid
LEFT JOIN aaauser ti on sdu.userid=ti.user_id
left JOIN prioritydefinition pd ON wos.priorityid = pd.priorityid
where wo.duebytime >= (UNIX_TIMESTAMP(date_format(curdate() - interval 8 day,'%Y-%m-%d'))*1000)
AND wo.duebytime <= (UNIX_TIMESTAMP(curdate()-interval 1 day)*1000)
and wot.workorderid = wot.thd_woid
and wos.isoverdue = 1
order by wot.workorderid'
at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7598)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.runSQLQueryReport(ReportRequestHandler.java:1606)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.handleRequest(ReportRequestHandler.java:1921)
at com.adventnet.servicedesk.reports.action.CustomReportHandler.handleRequest(CustomReportHandler.java:187)
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.AccountUrlFilter.doFilter(AccountUrlFilter.java:55)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
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.filter.ThreadLocalFilter.doFilter(ThreadLocalFilter.java:117)
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:154)
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 com.adventnet.servicedesk.tools.LoginCookiesValveBase.invoke(LoginCookiesValveBase.java:142)
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)
The query is:
select
wot.workorderid "Request ID",
name "Site",
ifnull(first_name, 'Unassigned') "Analyst",
case pd.PRIORITYNAME when '1. Critical' then 'Critical'
when '2. High' then 'High'
when '3. Medium' then 'Medium'
when '4. Low' then 'Low'
when 'Change' then 'Change'
End "Priority",
from_unixtime(wo.createdtime/1000,'%d-%m-%Y %H:%i:%s') "Open Date",
(case when wo.completedtime!=0 then from_unixtime(wo.completedtime/1000,'%d-%m-%Y %H:%i:%s') else 'Not Yet Completed' end) "Completed Date",
from_unixtime(wo.duebytime/1000,'%d-%m-%Y %H:%i:%s') "SLA Date",
DATEDIFF((CASE WHEN wo.completedtime > 0 THEN FROM_UNIXTIME(wo.completedtime/1000) ELSE curdate() END),FROM_UNIXTIME(wo.duebytime/1000)) "Days Over Target SLA"
from workorder_threaded wot
inner join workorder wo on wo.workorderid = wot.workorderid
left join workorderstates wos on wo.workorderid= wos.workorderid
LEFT JOIN sitedefinition sd on wo.siteid = sd.siteid
left join sdorganization sdo on sdo.org_id = sd.siteid
left join sduser sdu on sdu.userid = wos.ownerid
LEFT JOIN aaauser ti on sdu.userid=ti.user_id
left JOIN prioritydefinition pd ON wos.priorityid = pd.priorityid
where wo.duebytime >= (UNIX_TIMESTAMP(date_format(curdate() - interval 8 day,'%Y-%m-%d'))*1000)
AND wo.duebytime <= (UNIX_TIMESTAMP(curdate()-interval 1 day)*1000)
and wot.workorderid = wot.thd_woid
and wos.isoverdue = 1
order by wot.workorderid
The error I am getting is:
java.lang.Exception: Only select queries can be executed - 'select
wot.workorderid "Request ID",
name "Site",
ifnull(first_name, 'Unassigned') "Analyst",
case pd.PRIORITYNAME when '1. Critical' then 'Critical'
when '2. High' then 'High'
when '3. Medium' then 'Medium'
when '4. Low' then 'Low'
when 'Change' then 'Change'
End "Priority",
from_unixtime(wo.createdtime/1000,'%d-%m-%Y %H:%i:%s') "Open Date",
(case when wo.completedtime!=0 then from_unixtime(wo.completedtime/1000,'%d-%m-%Y %H:%i:%s') else 'Not Yet Completed' end) "Completed Date",
from_unixtime(wo.duebytime/1000,'%d-%m-%Y %H:%i:%s') "SLA Date",
DATEDIFF((CASE WHEN wo.completedtime > 0 THEN FROM_UNIXTIME(wo.completedtime/1000) ELSE curdate() END),FROM_UNIXTIME(wo.duebytime/1000)) "Days Over Target SLA"
from workorder_threaded wot
inner join workorder wo on wo.workorderid = wot.workorderid
left join workorderstates wos on wo.workorderid= wos.workorderid
LEFT JOIN sitedefinition sd on wo.siteid = sd.siteid
left join sdorganization sdo on sdo.org_id = sd.siteid
left join sduser sdu on sdu.userid = wos.ownerid
LEFT JOIN aaauser ti on sdu.userid=ti.user_id
left JOIN prioritydefinition pd ON wos.priorityid = pd.priorityid
where wo.duebytime >= (UNIX_TIMESTAMP(date_format(curdate() - interval 8 day,'%Y-%m-%d'))*1000)
AND wo.duebytime <= (UNIX_TIMESTAMP(curdate()-interval 1 day)*1000)
and wot.workorderid = wot.thd_woid
and wos.isoverdue = 1
order by wot.workorderid'
at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7598)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.runSQLQueryReport(ReportRequestHandler.java:1606)
at com.adventnet.servicedesk.reports.action.ReportRequestHandler.handleRequest(ReportRequestHandler.java:1921)
at com.adventnet.servicedesk.reports.action.CustomReportHandler.handleRequest(CustomReportHandler.java:187)
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.AccountUrlFilter.doFilter(AccountUrlFilter.java:55)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
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.filter.ThreadLocalFilter.doFilter(ThreadLocalFilter.java:117)
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:154)
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 com.adventnet.servicedesk.tools.LoginCookiesValveBase.invoke(LoginCookiesValveBase.java:142)
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)