/build/static/layout/Breadcrumb_cap_w.png

SQL Help Please

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


0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 8 months ago
Red Belt
0

Can you double check your query in the rule and make sure that this isn't the last line?

 and (HD_TICKET.ID = 17079) 

According to the error from the appliance, that is the last line of your query, which would cause an error because it is after the ORDER BY and LIMIT clauses.

You also have an odd select statement after your joins, which could also be a problem. 

 
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