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
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share