MySQL error has me stumped.
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
-
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. - JasonEgg 8 years ago
-
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. - glenhooper 8 years ago
Answers (2)
Please log in to answer
Posted by:
Hobbsy
8 years ago
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"
Posted by:
StockTrader
8 years ago
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
