/build/static/layout/Breadcrumb_cap_w.png

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 ()




0 Comments   [ + ] Show comments

Answers (1)

Posted by: JasonEgg 6 years ago
Red Belt
0
The last line of your UPDATE query is "WHERE ID in ()" which will always return nothing because nothing is specified in the parenthesis. You need to feed the results of your SELECT statement to the update query, which is done by using the variable <TICKET_IDS>

So change the last line of your UPDATE query to "WHERE ID in (<TICKET_IDS>)"

Comments:
  • Sorry for just replying and many thanks for this. I've added this but still get the same log message as above. - ms34 6 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 6 years ago
      • Unfortunately I get the same outcome. Many thanks. - ms34 6 years ago
      • Hmm. Maybe try including only "HD_TICKET.ID AS ID" in the SELECT clause. - JasonEgg 6 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 6 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 6 years ago
      • That worked - many thanks for your help. - ms34 6 years ago

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