/build/static/layout/Breadcrumb_cap_w.png

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?


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 4 years ago
Red Belt
0

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 4 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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