I am creating a custom rule to replace the generic Email on Events rules.  This script works fine in MySQL Workbench and pulls records properly and also works fine if I run the SQL in an SQL report within KACE.  The problem comes into place when I try running it in a Custom Ticket Rule.  I have it set to run on ticket save.  Here is the error I get in the Run Log: mysql error: [1054: Unknown column 'HD_TICKET.ID' in 'having clause']

When I scroll down the log to the section of the script that houses this part, here is what it shows:

WHERE
    C.DESCRIPTION NOT LIKE 'TICKET CREATED   /* this is necessary when using group by functions */
    GROUP BY M.ID
    HAVING 1=1 and (HD_TICKET.ID = 4067) ")

I know this ticket ID is valid because it is on a test ticket I am using. Below is the actual SQL I am running.

Thank you in advance for any help that can be provided.

SELECT
    -- Ticket Fields
    M.ID, -- $id
    /* M.ID AS TICKNUM, -- $ticknum */
    M.TITLE, -- $title
 
    -- Change Fields
    C.COMMENT, -- $comment
    C.DESCRIPTION, -- $description
    S.NAME AS STATUS,
 
    CASE WHEN S.NAME = 'New' THEN
     /* I have taken out this part of the script because it is a group concat to          create a custom message for the outgoing email /*
    END HISTORY, -- $history
   
    -- About the Udater
    UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
    UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
    UPDATER.EMAIL AS UPDATER_EMAIL,     -- $updater_email
    IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
 
    -- about the owner
    OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
    OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
    OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email
    IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
 
    -- About the Submitter
    SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
    SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
    SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email
 
    -- About Status
    S.NAME AS STATUS,   -- $status     -- About Category
    CAT.NAME AS CATEGORY, -- $category
 
FROM HD_TICKET M
 
    /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = M.ID
    /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = M.ID
    /* status **********/ JOIN HD_STATUS S ON S.ID = M.HD_STATUS_ID
    /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID = M.HD_CATEGORY_ID
    /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = M.OWNER_ID
    /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = M.SUBMITTER_ID
    /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID WHERE
    C.DESCRIPTION NOT LIKE 'TICKET CREATED%'
     
   /* this is necessary when using group by functions */
    GROUP BY M.ID
    HAVING 1=1

 

Answer Summary:
Replaced any instance of the M. alias with the HD_TICKET. I also changed the WHERE 1=1 to HAVING 1=1. Thanks to jverbosk and chucksteel for their help.
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

Try removing the alias for HD_TICKET and run the query again.  When I run into these type of errors it's the first thing I try and it usually gets things working (assuming the query works in the query browser).

John

Answered 10/08/2012 by: jverbosk
Red Belt

  • This is what I meant - FYI, I modified the CASE statement to use S.NAME in the THEN statement so this would work on my end:

    SELECT HD_TICKET.ID, HD_TICKET.TITLE,
    C.COMMENT, C.DESCRIPTION, S.NAME AS STATUS,
    CASE WHEN S.NAME = 'New' THEN S.NAME END 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,
    S.NAME AS STATUS,
    CAT.NAME AS CATEGORY,
    'help.line@nbexcellence.org' AS NEWTICKETEMAIL
    FROM HD_TICKET
    JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
    JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
    JOIN HD_STATUS S ON S.ID = HD_TICKET.HD_STATUS_ID
    JOIN HD_CATEGORY CAT ON CAT.ID = HD_TICKET.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
    WHERE C.DESCRIPTION NOT LIKE 'TICKET CREATED%'
    GROUP BY HD_TICKET.ID

    John
  • I changed removed the M alias from the script, but when the script runs on a ticket save, it shows the alias in the run log. Even though it does not show in the Select Query. I'm going to try with a brand new ticket to see what happens.
  • A brand new ticket works with the updated script. Kind of weird that the already opened ticket wouldn't. This is a major hurdle out of the way, now I can move on to actually getting this to email the info I want. Thanks John
Please log in to comment

Answers

2

Shouldn't it be WHERE 1 = 1 (instead of having 1=1)?

Answered 10/08/2012 by: chucksteel
Red Belt

  • I tried a WHERE instead of HAVING and it came back with this error:

    mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 1=1 and (HD_TICKET.ID = 4067)' at line 65]
  • It appears that the WHERE 1=1 did need to be changed to HAVING 1=1. This in concurrance with John's suggestion helped.
Please log in to comment
Answer this question or Comment on this question for clarity