/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


MySQL error has me stumped.

12/10/2015 789 views
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.

All 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

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ