/build/static/layout/Breadcrumb_cap_w.png

Error when running on ticket save update query

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>))



0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 9 years ago
Red Belt
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.

Comments:
  • That I good to know as I will often add a limit 1 on update just in case - Jbr32 9 years ago
  • Something so simple! Thanks Chucksteel - Joshy Boy 9 years ago
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ