error when running the following query report
When recently updated our Servicedesk to Build 9414 and now we get an error when trying to run the following query report.
Query:
SELECT projectdet .PROJECTID "Project Id",projectdet.TITLE "Project Title",projectstatus.STATUSNAME "Project Status",projectowner.FIRST_NAME "Project Owner",c.comment "Comments",md.Milestoneid "Milestone ID",
md.TITLE "Milestone Title",
mdon.FIRST_NAME "Milestone Owner",
mst.StatusName "Milestone Status" ,
LONGTODATE(MD.Createddate) "Milestone created time" ,
LONGTODATE(md.Scheduledstarttime) "Milestone Schedule start time" ,
LONGTODATE(md.Scheduledendtime) "Milestone Schedule end time" ,
LONGTODATE(md.Actualstarttime) "Milestone Actual start time",
LONGTODATE(md.Actualendtime) "Milestone Actual end time",
taskdet.TASKID "Task ID",taskdet.TITLE "Title",
taskowner.FIRST_NAME "Owner",
LONGTODATE(taskdet.SCHEDULEDSTARTTIME) "Scheduled Start Time",
LONGTODATE(taskdet.SCHEDULEDENDTIME) "Scheduled End Time",
LONGTODATE(taskdet.ACTUALSTARTTIME) "Actual Start Time",
LONGTODATE(taskdet.ACTUALENDTIME) "Actual End Time" FROM TaskDetails taskdet
RIGHT JOIN TaskToProjects projtotaskdet ON taskdet.TASKID=projtotaskdet.TASKID
LEFT JOIN ProjectDetails projectdet ON projtotaskdet.PROJECTID=projectdet .PROJECTID
LEFT JOIN TaskToProjects miletotaskdet ON taskdet.TASKID=miletotaskdet.TASKID
LEFT JOIN MileStoneDetails md ON miletotaskdet.MILESTONEID=md.MILESTONEID
LEFT JOIN SDUser projectownerdet ON projectdet.OWNERID=projectownerdet.USERID
LEFT JOIN AaaUser projectowner ON projectownerdet.USERID=projectowner.USER_ID
LEFT JOIN ProjectStatus projectstatus ON projectdet.STATUSID=projectstatus.STATUSID
LEFT JOIN SDUSer mdo ON md.OWNERID=mdo.USERID
LEFT JOIN AaaUser mdon ON mdo.USERID=mdon.USER_ID
LEFT JOIN ProjectStatus mst ON md.Statusid=mst.STATUSID
LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID
LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID
Left join projectcomments c on c.projectid = projectdet.projectid order by 1
Error:
java.sql.SQLException: Invalid object name 'projectcomments'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628) at net.sourceforge.jtds.jdbc.MSCursorResultSet.processOutput(MSCursorResultSet.java:933) at net.sourceforge.jtds.jdbc.MSCursorResultSet.cursorCreate(MSCursorResultSet.java:541) at net.sourceforge.jtds.jdbc.MSCursorResultSet.(MSCursorResultSet.java:154) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:365) at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1258) at org.jboss.resource.adapter.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:250) at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:8121) at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7950) at com.adventnet.servicedesk.reports.action.ReportRequestHandler.runSQLQueryReport(ReportRequestHandler.java:1730) at com.adventnet.servicedesk.reports.action.ReportRequestHandler.handleRequest(ReportRequestHandler.java:2090) at com.adventnet.servicedesk.reports.action.CustomReportHandler.handleRequest(CustomReportHandler.java:193) at com.adventnet.servicedesk.reports.action.ReportHandler.execute(ReportHandler.java:60) at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58) at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67) at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51) at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191) at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:305) at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191) at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462) at javax.servlet.http.HttpServlet.service(HttpServlet.java:641) at javax.servlet.http.HttpServlet.service(HttpServlet.java:722) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.manageengine.servicedesk.common.filter.ResetThreadLocal.doFilter(ResetThreadLocal.java:26) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.adventnet.servicedesk.filter.EncodingFilter.doFilter(EncodingFilter.java:232) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.adventnet.iam.security.SecurityFilter.doFilter(SecurityFilter.java:249) at com.manageengine.servicedesk.filter.SdpSecurityFilter.doFilter(SdpSecurityFilter.java:173) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:350) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.adventnet.servicedesk.filter.RememberMe.doFilter(RememberMe.java:112) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.adventnet.servicedesk.authentication.NTLMV2CredentialAssociationFilter.doFilter(NTLMV2CredentialAssociationFilter.java:33) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at jespa.http.HttpSecurityService.doFilter(HttpSecurityService.java:1596) at com.adventnet.servicedesk.authentication.NTLMV2Filter.doFilter(NTLMV2Filter.java:218) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.adventnet.filters.ParamFilter.doFilter(ParamFilter.java:30) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.adventnet.authentication.filter.AssociateCredential.doFilter(AssociateCredential.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.manageengine.servicedesk.filter.CommonHeadersFilter.doFilter(CommonHeadersFilter.java:55) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:581) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at com.adventnet.servicedesk.tools.AuthenticateNtlm.invoke(AuthenticateNtlm.java:177) at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:336) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:579) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:307) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)
Query:
SELECT projectdet .PROJECTID "Project Id",projectdet.TITLE "Project Title",projectstatus.STATUSNAME "Project Status",projectowner.FIRST_NAME "Project Owner",c.comment "Comments",md.Milestoneid "Milestone ID",
md.TITLE "Milestone Title",
mdon.FIRST_NAME "Milestone Owner",
mst.StatusName "Milestone Status" ,
LONGTODATE(MD.Createddate) "Milestone created time" ,
LONGTODATE(md.Scheduledstarttime) "Milestone Schedule start time" ,
LONGTODATE(md.Scheduledendtime) "Milestone Schedule end time" ,
LONGTODATE(md.Actualstarttime) "Milestone Actual start time",
LONGTODATE(md.Actualendtime) "Milestone Actual end time",
taskdet.TASKID "Task ID",taskdet.TITLE "Title",
taskowner.FIRST_NAME "Owner",
LONGTODATE(taskdet.SCHEDULEDSTARTTIME) "Scheduled Start Time",
LONGTODATE(taskdet.SCHEDULEDENDTIME) "Scheduled End Time",
LONGTODATE(taskdet.ACTUALSTARTTIME) "Actual Start Time",
LONGTODATE(taskdet.ACTUALENDTIME) "Actual End Time" FROM TaskDetails taskdet
RIGHT JOIN TaskToProjects projtotaskdet ON taskdet.TASKID=projtotaskdet.TASKID
LEFT JOIN ProjectDetails projectdet ON projtotaskdet.PROJECTID=projectdet .PROJECTID
LEFT JOIN TaskToProjects miletotaskdet ON taskdet.TASKID=miletotaskdet.TASKID
LEFT JOIN MileStoneDetails md ON miletotaskdet.MILESTONEID=md.MILESTONEID
LEFT JOIN SDUser projectownerdet ON projectdet.OWNERID=projectownerdet.USERID
LEFT JOIN AaaUser projectowner ON projectownerdet.USERID=projectowner.USER_ID
LEFT JOIN ProjectStatus projectstatus ON projectdet.STATUSID=projectstatus.STATUSID
LEFT JOIN SDUSer mdo ON md.OWNERID=mdo.USERID
LEFT JOIN AaaUser mdon ON mdo.USERID=mdon.USER_ID
LEFT JOIN ProjectStatus mst ON md.Statusid=mst.STATUSID
LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID
LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID
Left join projectcomments c on c.projectid = projectdet.projectid order by 1
Error:
java.sql.SQLException: Invalid object name 'projectcomments'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628) at net.sourceforge.jtds.jdbc.MSCursorResultSet.processOutput(MSCursorResultSet.java:933) at net.sourceforge.jtds.jdbc.MSCursorResultSet.cursorCreate(MSCursorResultSet.java:541) at net.sourceforge.jtds.jdbc.MSCursorResultSet.(MSCursorResultSet.java:154) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:365) at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1258) at org.jboss.resource.adapter.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:250) at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:8121) at com.adventnet.servicedesk.reports.utils.CoreReportDesigner.generateSQLReport(CoreReportDesigner.java:7950) at com.adventnet.servicedesk.reports.action.ReportRequestHandler.runSQLQueryReport(ReportRequestHandler.java:1730) at com.adventnet.servicedesk.reports.action.ReportRequestHandler.handleRequest(ReportRequestHandler.java:2090) at com.adventnet.servicedesk.reports.action.CustomReportHandler.handleRequest(CustomReportHandler.java:193) at com.adventnet.servicedesk.reports.action.ReportHandler.execute(ReportHandler.java:60) at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58) at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67) at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51) at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191) at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:305) at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191) at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462) at javax.servlet.http.HttpServlet.service(HttpServlet.java:641) at javax.servlet.http.HttpServlet.service(HttpServlet.java:722) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.manageengine.servicedesk.common.filter.ResetThreadLocal.doFilter(ResetThreadLocal.java:26) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.adventnet.servicedesk.filter.EncodingFilter.doFilter(EncodingFilter.java:232) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.adventnet.iam.security.SecurityFilter.doFilter(SecurityFilter.java:249) at com.manageengine.servicedesk.filter.SdpSecurityFilter.doFilter(SdpSecurityFilter.java:173) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:350) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.adventnet.servicedesk.filter.RememberMe.doFilter(RememberMe.java:112) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.adventnet.servicedesk.authentication.NTLMV2CredentialAssociationFilter.doFilter(NTLMV2CredentialAssociationFilter.java:33) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at jespa.http.HttpSecurityService.doFilter(HttpSecurityService.java:1596) at com.adventnet.servicedesk.authentication.NTLMV2Filter.doFilter(NTLMV2Filter.java:218) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.adventnet.filters.ParamFilter.doFilter(ParamFilter.java:30) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.adventnet.authentication.filter.AssociateCredential.doFilter(AssociateCredential.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.manageengine.servicedesk.filter.CommonHeadersFilter.doFilter(CommonHeadersFilter.java:55) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:581) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at com.adventnet.servicedesk.tools.AuthenticateNtlm.invoke(AuthenticateNtlm.java:177) at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:336) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:579) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:307) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)