Custom Ticket Rule Syntax Error
Hello,
im trying to create a custom rule to move tickets from a general queue to specific queues based on category. Before that im trying to unterstand the sql code though and I'm having some difficulties.
When running the following SQL Code from the Select SQL Field in the Ticket Rule it doesnt run, however if I try to directly run it on the database with mysql workbench it works.
SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.SUMMARY,
HD_CATEGORY_ID
FROM HD_TICKET
When clicking "View Ticket Search Results" there appears a Kace Logo with "There were syntax errors in your query." error.
When modifiying the statement to this, it seems to run but still gives me this error: "There was an error retrieving the data for this page. Please refresh the page. If the error persists, try resetting the default page layout."
SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.SUMMARY,
HD_TICKET.HD_CATEGORY_ID
FROM HD_TICKET
WHERE HD_TICKET.ID = 10239
ORDER BY HD_TICKET.ID
Are there some syntax rules I'm not aware of when running SQL code through the ticket rule or is it something else entirely?
Regards and Thanks for any help.
EDIT: Nevermind It works, I think only showing the Search Results doesnt work, as maybe the default layout doesnt have the same columns as the return of my sql query.
Now im kind of stuck on the update query though. For some reason the update query doesnt update the selected row, any idea whats wrong on this statement:
UPDATE HD_TICKET, HD_CATEGORY
SET HD_TICKET.HD_QUEUE_ID = 3
WHERE HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
AND HD_TICKET.ID = <TICKET_IDS>
-
View Ticket results never (or rarely) works, so don't be put off by that. See if the select statement shows any records then you will know it is working. - Hobbsy 2 years ago
Answers (1)
Top Answer
Quick Update to how I solved it:
For the Select query I ignored that i wasnt able to show the Columns correctly, what seems to be important though is that you can only give the Ticket ID, since thats whats put into the variable for the update query. So its Probably best to only select one field and let that be the ID of thet Ticket.
The update query seems only to work when I use the sql code that gets generated from the wizard. update seems to set all tables from which you get Information. Short example that worked for me as well as complex select, if I or anyone ever needs it:
update HD_TICKET, HD_QUEUE as T5, HD_PRIORITY as T6
set HD_TICKET.HD_QUEUE_ID = T5.ID
where T5.NAME = 'QueueName'
AND T6.ID = (SELECT HD_PRIORITY.ID FROM HD_PRIORITY
WHERE HD_PRIORITY.Name = (SELECT HD_PRIORITY.Name
FROM HD_PRIORITY
WHERE HD_PRIORITY.ID = (SELECT HD_PRIORITY_ID
FROM HD_TICKET WHERE ID in (<TICKET_IDS>)))
AND HD_PRIORITY.HD_QUEUE_ID = (SELECT HD_QUEUE.ID FROM HD_QUEUE WHERE HD_QUEUE.Name= 'QueueName'))
AND (HD_TICKET.ID in (<TICKET_IDS>))
Select: This gets the Priority ID from the selected ticket, looks for the name of the priority of the ticket, checks for the priority with the same name from another ticket and sets the priority field of the ticket to that priority id. This basically makes it so that when moving the ticket the data isnt lost but rather adapts.
Might be a bit too much and I think it can be optimized but its enough for me.