I have a bunch of rules to notify supervisors when tickets sit in a status for a certain period of time. I want the rule to also send an email to the ticket owner. When I add this statement which i stole from another rule:

OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email

I get an error. I assumed it had to do with pulling the owner info from another table and tried to add this join statement to fix it:

LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID

Here is the full query:

 Select 'lwalters@wacoisd.org' AS LISA,

'cfrey@wacoisd.org as CHARLIE,

OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_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 = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'ajimenez')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'trott')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'mmccormick')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'sfelkner')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'ckluk')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'bgamboa')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'rdaniels')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'lmland')

AND T.HD_QUEUE_ID = 1

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

I answered my own question. The table HD_TICKET was aliased to T and when I changed HD_TICKET.OWNER_ID" to "T.OWNER_ID" it worked. It's almost like I'm learning SQL.

Answered 02/05/2013 by: lmland
Tenth Degree Black Belt

  • I am going through the same pains as I learn SQL on my KACE over the last month.

    It would appear you have the same thing I have in my brain where I just have to say something out loud or read it on a different format to kind of reset my mind.

    BTW - Doing this type of email reminder was made MUCH simpler for me by duplicating the embedded "WAITING OVERDUE" ticket rule and modifying it to my needs. Also making some templates in MYSQL (are you using mYSQL?) for basic selects have helped a lot.
  • I'm using MySQL Workbench. I've duplicated the default rules and modified them for other rules.
Please log in to comment
Answer this question or Comment on this question for clarity