What really refers to? HD_TICKET.ID or HD_TICKET_CHANGE.ID?
I've tried two rules, for the first rule, <TICKET_IDS> return HD_TICKET_CHANGE.ID, the second return HD_TICKET.ID, I'm really confused for that, anyone who can help that, thanks!
Rule 1 for Queue 1
select HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.CUSTOM_FIELD_VALUE0, C.ID, C.DESCRIPTION, CF.FIELD_CHANGED, CF.BEFORE_VALUE, CF.AFTER_VALUE
from HD_TICKET
join HD_TICKET_CHANGE C on HD_TICKET.ID=C.HD_TICKET_ID and C.ID=<CHANGE_ID>
join HD_TICKET_CHANGE_FIELD CF on C.ID=CF.HD_TICKET_CHANGE_ID
where
HD_TICKET.HD_QUEUE_ID=8 and CF.FIELD_CHANGED="STATUS_NAME" and CF.BEFORE_VALUE!="Closed" and CF.AFTER_VALUE="Closed"
update HD_TICKET
join HD_TICKET_CHANGE C on HD_TICKET.ID=C.HD_TICKET_ID and C.ID=<TICKET_IDS>
set
HD_TICKET.CUSTOM_FIELD_VALUE0 = CONCAT('a', HD_TICKET.CUSTOM_FIELD_VALUE0)
Rule 2 for Queue 2
select HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.CUSTOM_FIELD_VALUE0, HD_TICKET.CUSTOM_FIELD_VALUE1
from HD_TICKET
inner join HD_TICKET_CHANGE C on HD_TICKET.ID=C.HD_TICKET_ID and C.ID=<CHANGE_ID>
where
HD_TICKET.HD_QUEUE_ID=9 and HD_TICKET.CUSTOM_FIELD_VALUE0=1 and HD_TICKET.CUSTOM_FIELD_VALUE1!=""
update HD_TICKET
join HD_TICKET_CHANGE C on C.HD_TICKET_ID=HD_TICKET.ID
set
HD_TICKET.CUSTOM_FIELD_VALUE0="",
HD_TICKET.CUSTOM_FIELD_VALUE2=CONCAT('--- ', NOW(), ' ---', ' Email to user as: ', HD_TICKET.CUSTOM_FIELD_VALUE1, ' ********** ', HD_TICKET.CUSTOM_FIELD_VALUE2),
HD_TICKET.CUSTOM_FIELD_VALUE1=""
where HD_TICKET.ID=<TICKET_IDS>
Answers (1)
The <TICKET_IDS> value will get set to the IDs of the tickets returned by the select rule. The value for <TICKET_IDS> doesn't make a difference in Rule 1 because you aren't changing anything in the HD_TICKET_CHANGE table so the broken join statement is kind of moot.
Comments:
-
I do another test, if in the select sentence, I only choose HD_TICKET table column, not choose HD_TICKET_CHANGE column, then the <TICKET_IDS> return HD_TICKET.ID. So it seems select sentence is the root cause for the return <TICKET_IDS>, may I right? - zhoda02 10 years ago
-
Yes, the results from selecting HD_TICKET.ID is placed into the <TICKET_IDS> variable. I seem to recall that whatever is returned as ID is actually what is placed in the <TICKET_IDS> variable so if you did something like SELECT USER.ID FROM USERS then <TICKET_IDS> would actually contain the user IDs selected. - chucksteel 10 years ago
-
Great, thanks for your reply! - zhoda02 10 years ago
