Hi,

We are trying to create a set of queries that will update fields in the asset table, when a save has been made in a help desk ticket.
The queries work perfectly fine when you select run now in the rules configuration. However when the rule is set to "on Ticket Save" we are receiving the following error message:


09/22/2014 12:19:47> Starting: 09/22/2014 12:19:47
09/22/2014 12:19:47> Executing Select Query...
09/22/2014 12:19:47> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and (HD_TICKET.ID = 64)' at line 23] in EXECUTE("SELECT        HD_TICKET.ID as ID,
            ASSET_DATA_9.FIELD_31,
            HD_TICKET.CUSTOM_FIELD_VALUE13,
            ASSET_DATA_9.FIELD_33,
            HD_TICKET.SUBMITTER_ID,
            MAX(MAXID)
           
FROM         ASSET_DATA_9
            JOIN
            HD_TICKET ON
                ASSET_DATA_9.FIELD_31 = HD_TICKET.CUSTOM_FIELD_VALUE13
            LEFT JOIN
            (
                select        max(HD_TICKET_CHANGE.ID) MAXID,
                            HD_TICKET_ID
                from        HD_TICKET_CHANGE
                GROUP BY    HD_TICKET_ID
            ) HTC_LATEST ON
                HD_TICKET.ID=HTC_LATEST.HD_TICKET_ID
            LEFT JOIN
            HD_TICKET_CHANGE HTC ON
                HTC.ID = HTC_LATEST.MAXID
ORDER BY    MAXID Desc and (HD_TICKET.ID = 64) ")



Below is the query we are trying to run, is there anything we need to add to allow the on ticket save feature to run?



SELECT  
 
    HD_TICKET.ID as ID,
                    ASSET_DATA_9.FIELD_31,

                    HD_TICKET.CUSTOM_FIELD_VALUE13,
                    ASSET_DATA_9.FIELD_33,
                    HD_TICKET.SUBMITTER_ID,

                    MAX(MAXID)
            
FROM       
 ASSET_DATA_9

                  JOIN
                  HD_TICKET ON
                      ASSET_DATA_9.FIELD_31 = HD_TICKET.CUSTOM_FIELD_VALUE13
                   LEFT JOIN
                   (
                        select            max(HD_TICKET_CHANGE.ID) MAXID,
                                             HD_TICKET_ID

                        from               HD_TICKET_CHANGE
                        GROUP BY    HD_TICKET_ID
                    ) HTC_LATEST
ON

                        HD_TICKET.ID=HTC_LATEST.HD_TICKET_ID
                    LEFT JOIN
                    HD_TICKET_CHANGE HTC ON
                    HTC.ID = HTC_LATEST.MAXID
ORDER BY   MAXID Desc


UPDATE     HD_TICKET, ASSET_DATA_9
SET           ASSET_DATA_9.FIELD_33 = HD_TICKET.SUBMITTER_ID,
                 ASSET_DATA_9.FIELD_34 = HD_TICKET.CUSTOM_FIELD_VALUE4,
                 ASSET_DATA_9.FIELD_38 = 'Yes'
WHERE    ASSET_DATA_9.FIELD_31 = HD_TICKET.CUSTOM_FIELD_VALUE13
and           (HD_TICKET.ID in (<TICKET_IDS>))


Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

3
When the rule is run on save KACE adds a line to limit the rule to only run on the ticket being saved. In the case of the error you posted it added "and (HD_TICKET.ID = 64)" to the query. Since it tacks this onto the end of the query it is generating an error because you have and ORDER BY clause in your select query. If you don't need the ORDER BY clause then remove it and you should be good.
Answered 09/22/2014 by: chucksteel
Red Belt

  • That I good to know as I will often add a limit 1 on update just in case
  • Something so simple! Thanks Chucksteel
Please log in to comment
Answer this question or Comment on this question for clarity