Error message on Query, but why?

Ok I have the following query:

SELECT machine,start,shift,a.ProdEmpFirstName + ' ' + a.ProdEmpLastName as mech1,mech2,verb1 as status FROM DJT_MECH_REPORT as i left outer join ProdEmployee AS a ON a.ProdEmployeeID = i.mech1 where datediff(day,prod_date,'{Root Container.Container.mechresp.Calendar.formattedDate}')=0 order by machine,start

Problem is if I run it, I get the QUERY MUST RETURN A RESULTS SET error message. Here is the dialog for that.

[java.sql.SQLException: The executeQuery method must return a result set. at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:429) at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1258) at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at com.inductiveautomation.factorypmi.gateway.servlets.Gateway$2.run(Gateway.java:1867) at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.doDBAction(Gateway.java:2291) at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.runQuery(Gateway.java:1852) at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.doPost(Gateway.java:332) at javax.servlet.http.HttpServlet.service(HttpServlet.java:709) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126) at com.inductiveautomation.factorypmi.gateway.ErrorReportValve.invoke(ErrorReportValve.java:95) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869) at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527) at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684) at java.lang.Thread.run(Unknown Source)

Now what is really odd is if i change the WHERE CLAUSE to look like:

where shift =1 and datepart(day,prod_date,'.....')

I do not get the error message and the query returns the correct information. So what I am missing? Does datepart need other where clause information to work properly?

In your quoted query you’re using datediff, but in your second where clause and at the end of your post you’re using datepart. Is it possible you’ve just mixed them up and the expression with datediff really doesn’t return any results?

Regards,

Sorry about that you are correct it is suppose to be datediff on both. And the issue is if I just use datediff, I get the No Results Returned, but if I simply add in the SHIFT = 1 AND statement it returns data. So why do I need to have two items in the where clause? I could not find anything on any of the SQL sites about needing to have a second item when using the datediff.

[quote=“Colby.Clegg”]In your quoted query you’re using datediff, but in your second where clause and at the end of your post you’re using datepart. Is it possible you’ve just mixed them up and the expression with datediff really doesn’t return any results?

If I use just the SHIFT = 1 I get data, but if I solely use the datediff I get nothing.

Regards,[/quote]

Hmm… does seem a bit strange. I noticed that in your “where” clause you don’t qualify the column name, perhaps that’s making some subtle difference. You could try qualifying it like “i.prod_date” and see if that helps.

Regards,