Error when running on ticket save update query
Hi,
We are trying to create a set of queries that will update fields in the asset table, when a save has been made in a help desk ticket.
The queries work perfectly fine when you select run now in the rules configuration. However when the rule is set to "on Ticket Save" we are receiving the following error message:
09/22/2014 12:19:47> Starting: 09/22/2014 12:19:47
09/22/2014 12:19:47> Executing Select Query...
09/22/2014 12:19:47> 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 'and (HD_TICKET.ID = 64)' at line 23] in EXECUTE("SELECT HD_TICKET.ID as ID,
ASSET_DATA_9.FIELD_31,
HD_TICKET.CUSTOM_FIELD_VALUE13,
ASSET_DATA_9.FIELD_33,
HD_TICKET.SUBMITTER_ID,
MAX(MAXID)
FROM ASSET_DATA_9
JOIN
HD_TICKET ON
ASSET_DATA_9.FIELD_31 = HD_TICKET.CUSTOM_FIELD_VALUE13
LEFT JOIN
(
select max(HD_TICKET_CHANGE.ID) MAXID,
HD_TICKET_ID
from HD_TICKET_CHANGE
GROUP BY HD_TICKET_ID
) HTC_LATEST ON
HD_TICKET.ID=HTC_LATEST.HD_TICKET_ID
LEFT JOIN
HD_TICKET_CHANGE HTC ON
HTC.ID = HTC_LATEST.MAXID
ORDER BY MAXID Desc and (HD_TICKET.ID = 64) ")
Below is the query we are trying to run, is there anything we need to add to allow the on ticket save feature to run?
SELECT HD_TICKET.ID as ID,
ASSET_DATA_9.FIELD_31,
HD_TICKET.CUSTOM_FIELD_VALUE13,
ASSET_DATA_9.FIELD_33,
HD_TICKET.SUBMITTER_ID,
MAX(MAXID)
FROM ASSET_DATA_9
JOIN
HD_TICKET ON
ASSET_DATA_9.FIELD_31 = HD_TICKET.CUSTOM_FIELD_VALUE13
LEFT JOIN
(
select max(HD_TICKET_CHANGE.ID) MAXID,
HD_TICKET_ID
from HD_TICKET_CHANGE
GROUP BY HD_TICKET_ID
) HTC_LATEST ON
HD_TICKET.ID=HTC_LATEST.HD_TICKET_ID
LEFT JOIN
HD_TICKET_CHANGE HTC ON
HTC.ID = HTC_LATEST.MAXID
ORDER BY MAXID Desc
UPDATE HD_TICKET, ASSET_DATA_9
SET ASSET_DATA_9.FIELD_33 = HD_TICKET.SUBMITTER_ID,
ASSET_DATA_9.FIELD_34 = HD_TICKET.CUSTOM_FIELD_VALUE4,
ASSET_DATA_9.FIELD_38 = 'Yes'
WHERE ASSET_DATA_9.FIELD_31 = HD_TICKET.CUSTOM_FIELD_VALUE13
and (HD_TICKET.ID in (<TICKET_IDS>))
We are trying to create a set of queries that will update fields in the asset table, when a save has been made in a help desk ticket.
The queries work perfectly fine when you select run now in the rules configuration. However when the rule is set to "on Ticket Save" we are receiving the following error message:
09/22/2014 12:19:47> Starting: 09/22/2014 12:19:47
09/22/2014 12:19:47> Executing Select Query...
09/22/2014 12:19:47> 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 'and (HD_TICKET.ID = 64)' at line 23] in EXECUTE("SELECT HD_TICKET.ID as ID,
ASSET_DATA_9.FIELD_31,
HD_TICKET.CUSTOM_FIELD_VALUE13,
ASSET_DATA_9.FIELD_33,
HD_TICKET.SUBMITTER_ID,
MAX(MAXID)
FROM ASSET_DATA_9
JOIN
HD_TICKET ON
ASSET_DATA_9.FIELD_31 = HD_TICKET.CUSTOM_FIELD_VALUE13
LEFT JOIN
(
select max(HD_TICKET_CHANGE.ID) MAXID,
HD_TICKET_ID
from HD_TICKET_CHANGE
GROUP BY HD_TICKET_ID
) HTC_LATEST ON
HD_TICKET.ID=HTC_LATEST.HD_TICKET_ID
LEFT JOIN
HD_TICKET_CHANGE HTC ON
HTC.ID = HTC_LATEST.MAXID
ORDER BY MAXID Desc and (HD_TICKET.ID = 64) ")
Below is the query we are trying to run, is there anything we need to add to allow the on ticket save feature to run?
SELECT HD_TICKET.ID as ID,
ASSET_DATA_9.FIELD_31,
HD_TICKET.CUSTOM_FIELD_VALUE13,
ASSET_DATA_9.FIELD_33,
HD_TICKET.SUBMITTER_ID,
MAX(MAXID)
FROM ASSET_DATA_9
JOIN
HD_TICKET ON
ASSET_DATA_9.FIELD_31 = HD_TICKET.CUSTOM_FIELD_VALUE13
LEFT JOIN
(
select max(HD_TICKET_CHANGE.ID) MAXID,
HD_TICKET_ID
from HD_TICKET_CHANGE
GROUP BY HD_TICKET_ID
) HTC_LATEST ON
HD_TICKET.ID=HTC_LATEST.HD_TICKET_ID
LEFT JOIN
HD_TICKET_CHANGE HTC ON
HTC.ID = HTC_LATEST.MAXID
ORDER BY MAXID Desc
UPDATE HD_TICKET, ASSET_DATA_9
SET ASSET_DATA_9.FIELD_33 = HD_TICKET.SUBMITTER_ID,
ASSET_DATA_9.FIELD_34 = HD_TICKET.CUSTOM_FIELD_VALUE4,
ASSET_DATA_9.FIELD_38 = 'Yes'
WHERE ASSET_DATA_9.FIELD_31 = HD_TICKET.CUSTOM_FIELD_VALUE13
and (HD_TICKET.ID in (<TICKET_IDS>))
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
10 years ago
When the rule is run on save KACE adds a line to limit the rule to only run on the ticket being saved. In the case of the error you posted it added "and (HD_TICKET.ID = 64)" to the query. Since it tacks this onto the end of the query it is generating an error because you have and ORDER BY clause in your select query. If you don't need the ORDER BY clause then remove it and you should be good.
