Rule not working since 5.4 upgrade
My joins are not working on this rule since going to 5.4. I have made several changes and still can't get it to work. Maybe another set of eyes on this will help.
select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
U3.FULL_NAME as UPDATERNAME,
U4.FULL_NAME as INITIALNAME,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
HD_TICKET_CHANGE.COMMENT,
HD_TICKET_CHANGE.DESCRIPTION as CHANGE_DESCRIPTION,
INITIAL_CHANGE.COMMENT as INITIAL_COMMENT,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
U2.LOCATION AS SUBMITTER_LOCATION,
U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_FIELD.NAME AS QUEUE_NAME
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID
left join USER U4 on U4.ID = INITIAL_CHANGE.USER_ID
left join HD_FIELD on HD_FIELD.ID = HD_TICKET.HD_FIELD_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and
HD_TICKET.OWNER_ID != HD_TICKET_CHANGE.USER_ID and
HD_TICKET_CHANGE.DESCRIPTION not like "%Ticket Created%" and
U1.EMAIL != 'tsc@ashland.edu' and
HD_TICKET.HD_QUEUE_ID = 1 and
HD_STATUS.NAME != 'Closed'
Answers (1)
I don't believe there is a column in HD_TICKET called HD_FIELD_ID. Also, HD_FIELD.NAME would be the name of a field, not the name of the queue. The queue name is HD_QUEUE.NAME. Should that join be to the queue table and not the field table?
Comments:
-
I changed and now I am getting the following message. This worked until we went to 5.4.
to your MySQL server version for the right syntax to use near '= JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID' at line 7] in EXECUTE("SELECT COUNT(*) FROM ( HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY) JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID and HD_TICKET_CHANGE.ID= JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) left join USER U1 on U1.ID = HD_TICKET.OWNER_ID left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID left join USER U4 on U4.ID = INITIAL_CHANGE.USER_ID left join HD_FIELD on HD_FIELD.ID = HD_TICKET.HD_QUEUE_ID where HD_PRIORITY.ID = HD_PRIORITY_ID and HD_STATUS.ID = HD_STATUS_ID and HD_IMPACT.ID = HD_IMPACT_ID and HD_CATEGORY.ID = HD_CATEGORY_ID and HD_TICKET.OWNER_ID != HD_TICKET_CHANGE.USER_ID and HD_TICKET_CHANGE.DESCRIPTION not like "%Ticket Created%" and U1.EMAIL != 'tsc@ashland.edu' and HD_TICKET.HD_QUEUE_ID = 1 and HD_STATUS.NAME != 'Closed' and HD_TICKET.HD_QUEUE_ID = '1' LIMIT 1") - scarpent 11 years ago-
Can you verify that the join statement for HD_TICKET_CHANGE is showing the <CHANGE_ID> in the rule? I'm not sure if ITNinja isn't showing it or if it isn't there. The error that you pasted is indicating a problem at that part of the rule. - chucksteel 11 years ago
-
Taking another look at this, the select statement shown here isn't the same as the one posted above. This rule seems to be finding a count, not ticket data. It looks more like something from a report and not a rule. - chucksteel 11 years ago
-
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID = <CHANGE_ID> - scarpent 11 years ago -
So what has happened is this got messed up when we went to 5.4. What it is suppose to do is send the ui link in an email to the client and the admin link to the owners of the tickets. - scarpent 11 years ago
-
Right, but the select statement in the original post is vastly different from the select statement you posted in our comment. - chucksteel 11 years ago
-
OK I think I found the problem I will test to see if it works now. Next time I am going to print my rules out before updating to a new version. - scarpent 11 years ago
-
You can make a SQL report that performs the following query to get an export of all of your rules:
SELECT * FROM ORG1.HD_TICKET_RULE;
(That's for ORG1, if you have multiple orgs, adjust accordingly). - chucksteel 11 years ago
-
Thanks for the input - scarpent 11 years ago
