Okay mySQL gurus, what did I do wrong below?

mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY OWNER_NAME LIMIT 0' at line 1] in EXECUTE( "SELECT HD_TICKET.ID, HD_TICKET.CREATED, S.FULL_NAME AS SUBMITTER_NAME, O.FULL_NAME AS OWNER_NAME, HD_TICKET.DUE_DATE, HD_STATUS.NAME AS STATUS_NAME, HD_TICKET.CUSTOM_FIELD_VALUE2, HD_TICKET.CUSTOM_FIELD_VALUE5 FROM HD_TICKET LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_STATUS.NAME not like '%close%')) AND ((TIMESTAMP(HD_TICKET.CREATED) > NOW() OR TIMESTAMP(HD_TICKET.CREATED) <= DATE_SUB(NOW(),INTERVAL 14 DAY)))) ORDER BY OWNER_NAME LIMIT 0")
2 Comments   [ + ] Show Comments

Comments

  • Why do you have "LIMIT 0" on the last line? This means "stop this query once you get zero results," which will always return no results.
  • I used the wizard to create this report. But not getting the results I wanted. I want a report that will list all tickets that are not closed that are 14 days old or older, sorted by technician name.
Please log in to comment

Answers

0
I think this is error is down to your brackets in the WHERE statement, try this version

WHERE
((HD_TICKET.HD_QUEUE_ID = 1) AND (HD_STATUS.NAME not like '%close%') AND ((TIMESTAMP(HD_TICKET.CREATED) > NOW() OR TIMESTAMP(HD_TICKET.CREATED) <= DATE_SUB(NOW(),INTERVAL 14 DAY))))

ORDER BY
OWNER_NAME LIMIT 0"

Answered 12/11/2015 by: Hobbsy
Red Belt

Please log in to comment
0
Try this one
SELECT HD_TICKET.ID, HD_TICKET.CREATED, S.FULL_NAME AS SUBMITTER_NAME, O.FULL_NAME AS OWNER_NAME, HD_TICKET.DUE_DATE, HD_STATUS.NAME AS STATUS_NAME, HD_TICKET.CUSTOM_FIELD_VALUE2, HD_TICKET.CUSTOM_FIELD_VALUE5 FROM HD_TICKET LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_STATUS.NAME not like '%close%')) AND ((TIMESTAMP(HD_TICKET.CREATED) > NOW() OR TIMESTAMP(HD_TICKET.CREATED) <= DATE_SUB(NOW(),INTERVAL 14 DAY))) ORDER BY OWNER_NAME LIMIT 0

Kind Regards,

Marco - StockTrader

Answered 12/11/2015 by: StockTrader
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share