We have a rule in place, that when a ticket is assigned from one owner to the next (i.e. transferred with in the team) that the new owner is notified via email that they have the ticket.

The rule is:

SELECT 
    HD_TICKET.ID,
    HD_TICKET.ID AS TICKNUM,
    HD_TICKET.TITLE,
    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,
    C.COMMENT,
    C.DESCRIPTION,
    GROUP_CONCAT(CONCAT('----- Change by ',
                UPDATER.EMAIL,
                ' at ',
                H.TIMESTAMP,
                ' -----
                ',
                H.DESCRIPTION,
                '
                ',
                H.COMMENT,
                '
                
                Please see your ticket at http://OURKBOX/userui/ticket.php?ID=',
                H.HD_TICKET_ID,
                '
                ')
        ORDER BY H.ID DESC
        SEPARATOR '
        ') HISTORY,
    UPDATER.USER_NAME AS UPDATER_UNAME,
    UPDATER.FULL_NAME AS UPDATER_FNAME,
    UPDATER.EMAIL AS UPDATER_EMAIL,
    IF(UPDATER.FULL_NAME = '',
        UPDATER.USER_NAME,
        UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL,
    OWNER.USER_NAME AS OWNER_UNAME,
    OWNER.FULL_NAME AS OWNER_FNAME,
    OWNER.EMAIL AS OWNER_EMAIL,
    IFNULL(OWNER.USER_NAME, 'Unassigned') OWNER_USER,
    SUBMITTER.USER_NAME AS SUBMITTER_UNAME,
    SUBMITTER.FULL_NAME AS SUBMITTER_FNAME,
    SUBMITTER.EMAIL AS SUBMITTER_EMAIL,
    P.NAME AS PRIORITY,
    S.NAME AS STATUS,
    I.NAME AS IMPACT,
    CAT.NAME AS CATEGORY,
    HD_QUEUE.NAME AS QUEUENAME
FROM
    HD_TICKET
        JOIN
    HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
        AND C.ID = <CHANGE_ID>
        JOIN
    HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
        JOIN
    HD_PRIORITY P ON P.ID = HD_PRIORITY_ID
        JOIN
    HD_STATUS S ON S.ID = HD_STATUS_ID
        JOIN
    HD_IMPACT I ON I.ID = HD_IMPACT_ID
        JOIN
    HD_CATEGORY CAT ON CAT.ID = HD_CATEGORY_ID
        LEFT JOIN
    USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
        LEFT JOIN
    USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
        LEFT JOIN
    USER UPDATER ON UPDATER.ID = C.USER_ID
        JOIN
    HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
WHERE
    (C.DESCRIPTION LIKE '%Changed ticket Owner%')
        and OWNER.USER_NAME is not null
GROUP BY OLIST.EMAIL
HAVING 1 = 1

I have put in bold Group By Olist.Email, as that appears to be where the issue is - guessing the syntax changed.  Below is the log error:

02/27/2015 09:41:15> Starting: 02/27/2015 09:41:15 02/27/2015 09:41:15> Executing Select Query... 02/27/2015 09:41:15> mysql error: [1054: Unknown column 'OLIST.EMAIL' in 'group statement'] in EXECUTE("SELECT HD_TICKET.ID, HD_TICKET.ID AS TICKNUM, HD_TICKET.TITLE, 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, C.COMMENT, C.DESCRIPTION, GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL, ' at ', H.TIMESTAMP, ' ----- ', H.DESCRIPTION, ' ', H.COMMENT, ' Please see your ticket at http://kbox/userui/ticket.php?ID=', H.HD_TICKET_ID, ' ') ORDER BY H.ID DESC SEPARATOR ' ') HISTORY, UPDATER.USER_NAME AS UPDATER_UNAME, UPDATER.FULL_NAME AS UPDATER_FNAME, UPDATER.EMAIL AS UPDATER_EMAIL, IF(UPDATER.FULL_NAME = '', UPDATER.USER_NAME, UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, OWNER.USER_NAME AS OWNER_UNAME, OWNER.FULL_NAME AS OWNER_FNAME, OWNER.EMAIL AS OWNER_EMAIL, IFNULL(OWNER.USER_NAME, 'Unassigned') OWNER_USER, SUBMITTER.USER_NAME AS SUBMITTER_UNAME, SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, SUBMITTER.EMAIL AS SUBMITTER_EMAIL, P.NAME AS PRIORITY, S.NAME AS STATUS, I.NAME AS IMPACT, CAT.NAME AS CATEGORY, HD_QUEUE.NAME AS QUEUENAME FROM HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID = 11422 JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID JOIN HD_PRIORITY P ON P.ID = HD_PRIORITY_ID JOIN HD_STATUS S ON S.ID = HD_STATUS_ID JOIN HD_IMPACT I ON I.ID = HD_IMPACT_ID JOIN HD_CATEGORY CAT ON CAT.ID = HD_CATEGORY_ID LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID WHERE (C.DESCRIPTION LIKE '%Changed ticket Owner%') and OWNER.USER_NAME is not null GROUP BY OLIST.EMAIL HAVING 1 = 1 and (HD_TICKET.ID = 4851) ")
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
Never mind.  Noticed that we have an email option in the queue setup, that will look after what we need.  Disabled the original rule.
Answered 03/03/2015 by: hutcha4113
Second Degree Blue Belt

Please log in to comment
Answer this question or Comment on this question for clarity