/build/static/layout/Breadcrumb_cap_w.png

K1000 rule: change ticket´s queue

I want to change the queue of a ticket and send it directly to SRI  queue (instead of the queue Suport 1° level, where every ticket goes at the begining) when the ticket´s tittle starts with [CTIC-SRI].

The select query works perfectly, select the correct tickets, but they doesnt change the queue (so the error has to be in the actualized query). i have seen so many post and i think that my code is the same to so many of these posts, for that reason i dont understand what is going on.

If somebody has any idea, would be fantastic!!
thank you in advance!!

My code:

->Selecionar SQL:
select                HD_TICKET.ID, 
                        HD_TICKET.TITLE,
                        Q.NAME as QUEUE_NAME,
                        Q.ID
                        
from (HD_TICKET)
                      LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID                      
 where  
                      ((  HD_TICKET.TITLE like '[CTIC-SRI]%') and HD_TICKET.HD_QUEUE_ID = 7 )


->Atualizar SQL:

update HD_TICKET , HD_QUEUE
set HD_TICKET.HD_QUEUE_ID = 5
where  HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID and  (HD_TICKET.ID in (<TICKET_IDS>))
Asked 9 years ago 2649 views
4 Comments   [ + ] Show comments
  • I don't think you need the HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID statement. You also aren't making any changes to HD_QUEUE, so there shouldn't be any need to include it in the first line. - chucksteel 9 years ago
  • oohhh chuck, thank you very much!! such a stupid mistakes!! - Paloma 9 years ago
  • umm... the ticket change the queue perfectly, but makes some changes in the priority and in the status, even with these changes in the code.

    ->Select SQL

    select HD_TICKET.ID,
    HD_TICKET.TITLE,
    HD_TICKET.HD_PRIORITY_ID

    from (HD_TICKET)
    JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
    JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
    JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
    where
    (( HD_TICKET.TITLE like '[CTIC-SRI]%') or ( HD_TICKET.TITLE like '[CTIC - SRI]%' )) and HD_TICKET.HD_QUEUE_ID = 7 and
    HD_TICKET.HD_PRIORITY_ID = HD_PRIORITY.ID AND
    HD_STATUS.ID = HD_TICKET.HD_STATUS_ID and
    HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID

    ->Actualized SQL

    update HD_TICKET, HD_PRIORITY

    set HD_TICKET.HD_QUEUE_ID = 5

    where HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID AND
    (HD_TICKET.ID in (<TICKET_IDS>)) - Paloma 9 years ago
    • Right, priority, status and category are all tied to the queue, so if you are going to change the queue with a rule then it should include some way to update the other values, too (I should have mentioned that). If the names match then you can try to update the ticket's values for those items based on the name, but that will make the update query much more complicated. - chucksteel 9 years ago
  • Yesss!!! finally i understood it and i made two rules, to move to different queues, one with the same values in the fields and the other with different values and both are working!!! NICE!! - Paloma 9 years ago

Answers (2)

Answer Summary:
buff, finally it is completely working!!!! as chucksteel said, when you change the queue you have to be careful and take care with priority, status and category. i have made two different rules because of depending on the tittle it can go to SRI queue or to SSA queue. the first one have the same values in the same fields than the original queue, but the second one has much more status (for example) than the original queue. so for the second one what i have done is directly set the defect values that i have desinged. The select query is the same for both (just change the part of the tittle that has to compare), the difference comes into the actualized query. ->Select SQL select HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.HD_PRIORITY_ID from (HD_TICKET) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) where (( HD_TICKET.TITLE like '[CTIC-SRI]%') or ( HD_TICKET.TITLE like '[CTIC - SRI]%' )) and HD_TICKET.HD_QUEUE_ID = 7 and HD_TICKET.HD_PRIORITY_ID = HD_PRIORITY.ID AND HD_STATUS.ID = HD_TICKET.HD_STATUS_ID and HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID ->Actualized SQL (SRI) update HD_TICKET, HD_PRIORITY, HD_CATEGORY, HD_STATUS, HD_IMPACT, HD_QUEUE set HD_TICKET.HD_QUEUE_ID = 5, HD_TICKET.HD_STATUS_ID = HD_QUEUE.DEFAULT_STATUS_ID, HD_TICKET.HD_CATEGORY_ID = HD_QUEUE.DEFAULT_CATEGORY_ID, HD_TICKET.HD_IMPACT_ID = HD_QUEUE.DEFAULT_IMPACT_ID, HD_TICKET.HD_PRIORITY_ID = HD_QUEUE.DEFAULT_PRIORITY_ID where HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID AND HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID AND HD_STATUS.ID = HD_TICKET.HD_STATUS_ID AND HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID AND (HD_TICKET.ID in ()) ->Actualized SQL (SSA) update HD_TICKET, HD_PRIORITY, HD_CATEGORY, HD_IMPACT, HD_QUEUE , HD_STATUS set HD_TICKET.HD_QUEUE_ID = 6, HD_TICKET.HD_STATUS_ID = 38, HD_TICKET.HD_CATEGORY_ID = 19, HD_TICKET.HD_IMPACT_ID = HD_QUEUE.DEFAULT_IMPACT_ID, HD_TICKET.HD_PRIORITY_ID = 13, HD_TICKET.CUSTOM_FIELD_VALUE5 = 'Institucional' where HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID AND HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID AND HD_STATUS.ID = HD_TICKET.HD_STATUS_ID AND HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID AND (HD_TICKET.ID in ())
Posted by: Paloma 9 years ago
Purple Belt
3
buff, finally it is completely working!!!! as chucksteel said, when you change the queue you have to be careful and take care with priority, status and category.
i have made two different rules because of depending on the tittle it can go to SRI queue or to SSA queue. the first one have the same values in the same fields than the original queue, but the second one has much more status (for example) than the original queue. so for the second one what i have done is directly set the defect values that i have desinged.
The select query is the same for both (just change the part of the tittle that has to compare), the difference comes into the actualized query.

->Select SQL

select                HD_TICKET.ID, 
                        HD_TICKET.TITLE, 
                        HD_TICKET.HD_PRIORITY_ID
                                          
from (HD_TICKET)
     JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
     JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
     JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)                    
 where  
           (( HD_TICKET.TITLE like '[CTIC-SRI]%') or (  HD_TICKET.TITLE like '[CTIC - SRI]%' )) and                                                        HD_TICKET.HD_QUEUE_ID = 7  and 
             HD_TICKET.HD_PRIORITY_ID = HD_PRIORITY.ID AND 
             HD_STATUS.ID = HD_TICKET.HD_STATUS_ID and 
             HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID


->Actualized SQL (SRI)

update HD_TICKET, HD_PRIORITY, HD_CATEGORY, HD_STATUS, HD_IMPACT, HD_QUEUE 

set HD_TICKET.HD_QUEUE_ID = 5,
     HD_TICKET.HD_STATUS_ID = HD_QUEUE.DEFAULT_STATUS_ID,
     HD_TICKET.HD_CATEGORY_ID = HD_QUEUE.DEFAULT_CATEGORY_ID,
     HD_TICKET.HD_IMPACT_ID = HD_QUEUE.DEFAULT_IMPACT_ID,
     HD_TICKET.HD_PRIORITY_ID = HD_QUEUE.DEFAULT_PRIORITY_ID

where  HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID  AND
           HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID AND
           HD_STATUS.ID = HD_TICKET.HD_STATUS_ID AND 
           HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID AND
           (HD_TICKET.ID in (<TICKET_IDS>))



->Actualized SQL (SSA)

update HD_TICKET, HD_PRIORITY, HD_CATEGORY, HD_IMPACT, HD_QUEUE , HD_STATUS 

set HD_TICKET.HD_QUEUE_ID = 6,
     HD_TICKET.HD_STATUS_ID = 38, 
     HD_TICKET.HD_CATEGORY_ID = 19,
     HD_TICKET.HD_IMPACT_ID = HD_QUEUE.DEFAULT_IMPACT_ID,
     HD_TICKET.HD_PRIORITY_ID = 13,
     HD_TICKET.CUSTOM_FIELD_VALUE5 = 'Institucional'

where   HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID  AND
           HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID AND
           HD_STATUS.ID = HD_TICKET.HD_STATUS_ID AND 
           HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID AND
           (HD_TICKET.ID in (<TICKET_IDS>))
Posted by: Paloma 9 years ago
Purple Belt
2
->Selecionar SQL:

select         HD_TICKET.ID, 
                  HD_TICKET.TITLE
                                          
from           (HD_TICKET)
                       
 where  
                 ((  HD_TICKET.TITLE like '[CTIC-SRI]%') and HD_TICKET.HD_QUEUE_ID = 7 )

->Atualizar SQL:

update HD_TICKET 
set HD_TICKET.HD_QUEUE_ID = 5
where  (HD_TICKET.ID in (<TICKET_IDS>))
Comments:
  • this query changes the queue, but it doesnt maintain the prioridade - Paloma 9 years ago
 
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