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>))
4 Comments
[ + ] Show comments
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 ())
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 ())
Please log in to answer
Posted by:
Paloma
10 years ago
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
10 years ago
->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 10 years ago
->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 10 years ago