/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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

Answer this question or Comment on this question for clarity

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