Looking for help modifying current SQL rule
Hello all,
We have a ticket rule in our service desk which emails the department if any ticket has not been 'modified' in the last 7 days. The rule works great but I wanted to make a 'simple' change. Since the rule has been created, we added a new custom field which defines the ticket as an Incident or Request. The line I would like to add to the code is to omit any tickets which are set as Requests.
Current SQL:
SELECT '*enter_email_here*' AS ES_EMAIL, S.NAME AS STATUS, T.ID AS ID, T.TITLE AS ISSUE, U.FULL_NAME AS SUBMITTER, O.FULL_NAME AS OWNER, T.CREATED AS CREATED, T.MODIFIED AS STALLED
FROM HD_TICKET T
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
JOIN USER O ON (O.ID = T.OWNER_ID)
WHERE (S.NAME != 'Closed' AND DATEDIFF(NOW(), T.MODIFIED) >= 7)
AND T.HD_QUEUE_ID = 1
After playing the the Wizard, it seems like the line I need to enter is: (HD_TICKET.CUSTOM_FIELD_VALUE4 != 'Request') but all the different methods of trying to inject it do not produce the desired effect. Can you please show me how to incorporate it?
Answers (1)
Top Answer
Explanation here, query below if you aren't interested.
The extra line needs to be added to the WHERE clause. The basic outline of a query is a combination of SELECT columns FROM tables WHERE conditions are true. In this case, you are adding a condition, HD_TICKET.CUSTOM_FIELD_VALUE4 != 'Request', except that your query doesn't know what HD_TICKET is.
When referring to columns you specify table name dot column name, e.g. HD_TICKET.CUSTOM_FIELD_VALUE4. Your query has created an alias for the HD_TICKET table and now calls it just T, that happens in the FROM line, which when stated in the full notation would look like this: FROM HD_TICKET as T.
It's kind of like a nickname, so when you're introduced to me as Chuck, but someone calls me Charles, you have now idea who they are talking about (which comes in handy when telemarketers call asking for Charles and my kids are like no one with that name lives here).
So that means we can't refer to the CUSTOM_FIELD_VALUE4 column in the HD_TICKET table as being in that table, we need to use the alias T for the table, which means adding it to the WHERE clause as T.CUSTOM_FIELD_VALUE4.
SELECT '*enter_email_here*' AS ES_EMAIL, S.NAME AS STATUS, T.ID AS ID, T.TITLE AS ISSUE, U.FULL_NAME AS SUBMITTER, O.FULL_NAME AS OWNER, T.CREATED AS CREATED, T.MODIFIED AS STALLED
FROM HD_TICKET T
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
JOIN USER O ON (O.ID = T.OWNER_ID)
WHERE (S.NAME != 'Closed' AND DATEDIFF(NOW(), T.MODIFIED) >= 7)
AND T.HD_QUEUE_ID = 1
AND T.CUSTOM_FIELD_VALUE4 != 'Request'
Comments:
-
Thank you so much Chuck! It makes total sense after your explanation. The script works perfectly now. - rbaranowicz 5 years ago
