Scripting

Ive created this script which is the select portion of an update rule but cant make it run in kace sma. It runs fine in MySQL Workbench but unless I remove the last two lines it wont run in KACE SMA. the last two lines are critical.  Any Ideas?

Im including the error that is displayed in kace sma.  AGain, if I remove the last two lines it runs and says it selected 2.. which is precisely why I need it to randomize the order and limit the select to 1. There could be as many as 7 depending on the criteria inside the ticket. In Mysql Wokbench the result is a single randomized user ID.    Merry Christmas. 

• SELECT
• DISTINCT
• ORG1.USER_LABEL_JT.USER_ID 
• 
  
• FROM
• (HD_TICKET, USER, ASSET)
• JOIN ASSET A1 ON ORG1.ASSET.ID = ORG1.USER.LOCATION_ID
• JOIN HD_TICKET HD1 ON ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3 = ASSET.NAME
• JOIN USER_LABEL_JT ON USER_LABEL_JT.USER_ID = USER.ID
• JOIN LABEL ON LABEL.ID AND USER_LABEL_JT.LABEL_ID
• 
  
• #join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID and C.ID = <CHANGE_ID>
• join HD_SERVICE_TICKET ST on HD_TICKET.SERVICE_TICKET_ID = ST.ID
• join HD_SERVICE SERVICE on ST.HD_SERVICE_ID = SERVICE.ID
• join HD_TICKET PARENT on HD_TICKET.PARENT_ID = PARENT.ID and PARENT.IS_PARENT
• join HD_STATUS S on HD_TICKET.HD_STATUS_ID = S.ID
• ,(select S.`VALUE` ticket_label from SETTINGS S where S.`NAME` = "HD_TICKET_LABEL") settings
• 
  
• where
• (ORG1.HD_TICKET.HD_QUEUE_ID = 35 AND
• ORG1.USER.LOCATION_ID = ORG1.ASSET.ID  and
• ORG1.HD_TICKET.ID = 17079 AND  <-----**** Note this is just here for MySql Workbench.. although, it should work fine in KACE as well. I replaced it in kace with both nothing and " and (HD_TICKET.ID in (<TICKET_IDS>))  "
• ORG1.USER.ID = USER_LABEL_JT.USER_ID AND USER_LABEL_JT.LABEL_ID = 223 AND ORG1.LABEL.ID = 223) AND
• (S.STATE = "Opened"
• and (SERVICE.ID = 21 or SERVICE.ID = 31) /* Employee onboard */
• and ST.ORDINAL = 3 /* Stage 3 */)
• ORDER BY rand()
• LIMIT 1

CODE DIRECTLY FROM KACE SMA FOR CLARITY

• SELECT
• DISTINCT
• ORG1.USER_LABEL_JT.USER_ID 
• 
  
• FROM
• (HD_TICKET, USER, ASSET)
• JOIN ASSET A1 ON ORG1.ASSET.ID = ORG1.USER.LOCATION_ID
• JOIN HD_TICKET HD1 ON ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3 = ASSET.NAME
• JOIN USER_LABEL_JT ON USER_LABEL_JT.USER_ID = USER.ID
• JOIN LABEL ON LABEL.ID AND USER_LABEL_JT.LABEL_ID
• 
  
• #join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID and C.ID = <CHANGE_ID>
• join HD_SERVICE_TICKET ST on HD_TICKET.SERVICE_TICKET_ID = ST.ID
• join HD_SERVICE SERVICE on ST.HD_SERVICE_ID = SERVICE.ID
• join HD_TICKET PARENT on HD_TICKET.PARENT_ID = PARENT.ID and PARENT.IS_PARENT
• join HD_STATUS S on HD_TICKET.HD_STATUS_ID = S.ID
• ,(select S.`VALUE` ticket_label from SETTINGS S where S.`NAME` = "HD_TICKET_LABEL") settings
• 
  
• where
• (ORG1.HD_TICKET.HD_QUEUE_ID = 35 AND
• ORG1.USER.LOCATION_ID = ORG1.ASSET.ID  and
• ORG1.USER.ID = USER_LABEL_JT.USER_ID AND USER_LABEL_JT.LABEL_ID = 223 AND ORG1.LABEL.ID = 223) AND
• (S.STATE = "Opened"
• and (SERVICE.ID = 21 or SERVICE.ID = 31) /* Employee onboard */
• and ST.ORDINAL = 3 /* Stage 3 */) AND
• (HD_TICKET.ID in (<TICKET_IDS>))  
• 
  
• ORDER BY rand() AND
• LIMIT 1

12/24/2021 07:27:13> Starting: 12/24/2021 07:27:13 12/24/2021 07:27:13> Executing Select Query... 12/24/2021 07:27:13> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<TICKET_IDS>)) ORDER BY rand() AND LIMIT 1 and (HD_TICKET.ID = 17079)' at line 26] in EXECUTE("SELECT DISTINCT ORG1.USER_LABEL_JT.USER_ID FROM (HD_TICKET, USER, ASSET) JOIN ASSET A1 ON ORG1.ASSET.ID = ORG1.USER.LOCATION_ID JOIN HD_TICKET HD1 ON ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3 = ASSET.NAME JOIN USER_LABEL_JT ON USER_LABEL_JT.USER_ID = USER.ID JOIN LABEL ON LABEL.ID AND USER_LABEL_JT.LABEL_ID #join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID and C.ID = 157395 join HD_SERVICE_TICKET ST on HD_TICKET.SERVICE_TICKET_ID = ST.ID join HD_SERVICE SERVICE on ST.HD_SERVICE_ID = SERVICE.ID join HD_TICKET PARENT on HD_TICKET.PARENT_ID = PARENT.ID and PARENT.IS_PARENT join HD_STATUS S on HD_TICKET.HD_STATUS_ID = S.ID ,(select S.`VALUE` ticket_label from SETTINGS S where S.`NAME` = "HD_TICKET_LABEL") settings where (ORG1.HD_TICKET.HD_QUEUE_ID = 35 AND ORG1.USER.LOCATION_ID = ORG1.ASSET.ID and ORG1.USER.ID = USER_LABEL_JT.USER_ID AND USER_LABEL_JT.LABEL_ID = 223 AND ORG1.LABEL.ID = 223) AND (S.STATE = "Opened" and (SERVICE.ID = 21 or SERVICE.ID = 31) /* Employee onboard */ and ST.ORDINAL = 3 /* Stage 3 */) AND (HD_TICKET.ID in (<TICKET_IDS>)) ORDER BY rand() AND LIMIT 1 and (HD_TICKET.ID = 17079) ")