I am getting an error in the update query for several rules which seems to involve this proprietary variable <TICKET_IDS>. For example, I have a rule to set certain submitters to a high priority based on a VIP label. Here's the update query that is giving an error:

update HD_TICKET, HD_PRIORITY as T5
    set HD_TICKET.HD_PRIORITY_ID = T5.ID
  where T5.NAME = 'High' and
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
        (HD_TICKET.ID in (<TICKET_IDS>))

Others have said that these variables will always give an error except when the rule runs and turns that variable into an array. How do I test to make sure this is the case and the rule is functioning properly?

5 Comments   [ + ] Show Comments

Comments

  • It also seems like the way the wizard and default rules have the update queries written, the variable that needs to be set is in the WHERE clause. It might be that I don't understand SQL well enough.

    I created a vacation rule and had to change the update query to "set HD_TICKET.OWNER_ID = 0" to get it to work right. The way the wizard wrote it or copying from another default rule it had the variable the update query should be setting in the WHERE clause which seems like it would never work in my mind.

    Is this something not right in the wizard or default rules? Seems like that should get fixed if it is.
  • Does the select query return the appropriate ticket ids? <TICKET_IDS> is replaced by the ticket ids returned in the select query of the ticket rule. What is the exact error error message?
  • The error message was "error in syntax at line 10 "))". It seems to have resolved itself. I think the problem is when <TICKET_IDS> was empty it meant that the parentheses were around a null value or something. Just ran it this morning on a ticket and it worked.
  • that's exactly right: It will error if empty, so in order to test it you must have a ticket that meets the criteria for the KACE inner workings to populate the IDS field.
  • Remember also that the TICKET_IDS variable will correspond to the Select query. Specifically, it will be the last Select value with the label of 'ID'.

    So, if you have HD_TICKET.ID, HD_STATUS.ID in your select statement it would correspond to she STATUS. That's one reason thou see things like "HD_STATUS.ID as HD_STATUS_ID"; that keeps HD_TICKET.ID as the ID.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity

Share