Help with update statement

Not really good at this sql stuff. But this update statement is moving the tickets to the required queue but in the closed status. any ideas how i can fix this. any help will be appreciated...

update

HD_TICKET T

join HD_CATEGORY CATS 

on T.HD_CATEGORY_ID = CATS.ID 

join HD_STATUS SS 

on T.HD_STATUS_ID = SS.ID 

join HD_PRIORITY PS 

on T.HD_PRIORITY_ID = PS.ID 

join HD_IMPACT IMS 

on T.HD_IMPACT_ID = IMS.ID, HD_QUEUE QD, HD_CATEGORY CATD, HD_STATUS SD, HD_PRIORITY PD, HD_IMPACT IMD 

set T.HD_CATEGORY_ID = CATD.ID, T.HD_STATUS_ID = SD.ID, T.HD_PRIORITY_ID = PD.ID, T.HD_IMPACT_ID = IMD.ID, T.HD_QUEUE_ID = QD.ID where T.ID = <TICKET_IDS> and QD.NAME = 'Sales Service SCS'



Thanks 

4 Comments   [ + ] Show Comments

Comments

  • I am confused on how this is handled. the ticket comes in to the Helpdesk (31) queue by email and it has a default value of 1, which is in the state of opened. When the this query runs

    select
    HD_TICKET.ID
    from
    HD_TICKET
    join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID
    and C.ID = <CHANGE_ID>
    where
    HD_TICKET.TITLE rlike ('test')
  • Then the update runs
    update
    HD_TICKET T
    join HD_CATEGORY CATS
    on T.HD_CATEGORY_ID = CATS.ID
    join HD_STATUS SS
    on T.HD_STATUS_ID = SS.ID
    join HD_PRIORITY PS
    on T.HD_PRIORITY_ID = PS.ID
    join HD_IMPACT IMS
    on T.HD_IMPACT_ID = IMS.ID, HD_QUEUE QD, HD_CATEGORY CATD, HD_STATUS SD, HD_PRIORITY PD, HD_IMPACT IMD
    set T.HD_CATEGORY_ID = CATD.ID, T.HD_STATUS_ID = SD.ID, T.HD_PRIORITY_ID = PD.ID, T.HD_IMPACT_ID = IMD.ID, T.HD_QUEUE_ID = QD.ID where T.ID = <TICKET_IDS> and QD.NAME = 'other queue’

    the status is changed to closed. Looking at the other queue (40) default status is set to 150 which is opened. Where is the status of close coming from? The update is just passing the same status from the prior queue…I am so confused. Please help.
    • The status is getting set as T.HD_STATUS_ID = SD.ID but looking at the query I don't see where that value is getting populated. SD is an alias to HD_STATUS. After the rule runs what is the value for HD_TICKET.HD_STATUS_ID for the ticket?
  • is sat to 141 = which is the state of closed for that queue. can I just do this in the statement T.HD_STATUS_ID = '140' , would this work?
  • Jus got this from dell support: "the ticket rule says to update status.id=status.id , those id's are unique to each queue , so if id
    2 in queue 1 is NEW and the id 2 in queue 2 is Opened the ticket rule will do exactly what you told to do set id to id , hope that makes sense."
Please log in to comment

Answers

0
Status IDs are unique to each queue, take a look at the HD_STATUS table to check which status values pertain to the new queue and set the ID to the correct status. 
Answered 12/17/2015 by: chucksteel
Red Belt

  • Thanks chucksteel...I am a little worry of making any changes to the statement. I see the status table has 2 status values on for close and one for open. not sure how to enter the correct value...
Please log in to comment
Answer this question or Comment on this question for clarity