Automatically change to 2nd queue if a certain category is selected
Hi,
I am trying to get tickets moved, on ticket save, automatically if a certain category is selected.
This is this code I'm using:
SELECT HD_TICKET.ID, HD_TICKET.TITLE,
HD_TICKET.HD_CATEGORY_ID, HD_TICKET.HD_QUEUE_ID,
HD_CATEGORY.ID, HD_CATEGORY.NAME
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE HD_CATEGORY.NAME like '%IT Project%'
AND HD_TICKET.HD_QUEUE_ID = 1
ORDER BY HD_TICKET.ID
UPDATE HD_TICKET, HD_CATEGORY
SET HD_TICKET.HD_QUEUE_ID = 2
WHERE HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
AND HD_TICKET.ID in (
This is the output log I get which shows no errors:
30/05/2017 15:03:20> Starting: 30/05/2017 15:03:20 30/05/2017 15:03:20> Executing Select Query... 30/05/2017 15:03:20> selected 2 rows 30/05/2017 15:03:20> Executing Update Query... 30/05/2017 15:03:20> updated 0 rows 30/05/2017 15:03:20> Ending: 30/05/2017 15:03:20
If anyone could point me in the right direction it would be much appreciated.
Many thanks.
31/05/17
For further info I've also tried with the following update query which I believe should just change the queue but get the same output log as above.
UPDATE HD_TICKET
SET HD_QUEUE_ID = 2
WHERE ID in (
Answers (1)
Comments:
-
Sorry for just replying and many thanks for this. I've added this but still get the same log message as above. - ms34 7 years ago
-
Try aliasing "HD_TICKET.ID" by changing it to "HD_TICKET.ID AS ID" in the SELECT clause. I think <TICKET_IDS> only draws from a column if its exact name is "ID" - JasonEgg 7 years ago
-
Unfortunately I get the same outcome. Many thanks. - ms34 7 years ago
-
Hmm. Maybe try including only "HD_TICKET.ID AS ID" in the SELECT clause. - JasonEgg 7 years ago
-
Same message log as before with only "HD_TICKET.ID AS ID":
09/06/2017 09:48:18> Starting: 09/06/2017 09:48:18 09/06/2017 09:48:18> Executing Select Query... 09/06/2017 09:48:18> selected 1 rows 09/06/2017 09:48:18> Executing Update Query... 09/06/2017 09:48:18> updated 0 rows 09/06/2017 09:48:18> Ending: 09/06/2017 09:48:18
This is what the select clause currently looks like:
SELECT HD_TICKET.ID AS ID,
HD_TICKET.HD_CATEGORY_ID, HD_TICKET.HD_QUEUE_ID,
HD_CATEGORY.ID, HD_CATEGORY.NAME
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE HD_CATEGORY.NAME like '%IT Project%'
AND HD_TICKET.HD_QUEUE_ID = 1
ORDER BY HD_TICKET.ID - ms34 7 years ago -
change the select query to:
SELECT HD_TICKET.ID AS ID
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE HD_CATEGORY.NAME like '%IT Project%'
AND HD_TICKET.HD_QUEUE_ID = 1 - JasonEgg 7 years ago -
That worked - many thanks for your help. - ms34 7 years ago
