Custom Email to Submitter on Owner Change
I am trying to write a Service Desk rule to send a custom email when a ticket is New and the owner is changed from a value of 'DefaultTicketOwners' to an actual owner. We do not use the 'Unassigned' value and have a different rule to auto-assign any of those tickets to 'DefaultTicketOwners'. Here is what I have been able to put together so far:
select distinct HD_TICKET.ID, HD_TICKET.OWNER_ID as OWNER_ID, HD_TICKET.SUBMITTER_ID as SUBMITTER_ID, HD_TICKET.ID as TICKNUM, HD_TICKET.TITLE, OWNER.USER_NAME as OWNER_NAME, OWNER.FULL_NAME as OWNER_FULLNAME, OWNER.EMAIL as OWNER_EMAIL, UPDATER.USER_NAME as UPDATERNAME, UPDATER.EMAIL as UPDATEREMAIL, SUBMITTER.FULL_NAME as SUBMITTER_FULLNAME, SUBMITTER.EMAIL as SUBMITTER_EMAIL from (HD_TICKET) left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = <CHANGE_ID> left join HD_TICKET_CHANGE_FIELD on HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID left join USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID left join USER OLD_OWNER on OLD_OWNER.ID = HD_TICKET_CHANGE.OWNER_ID left join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID left join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID where HD_TICKET_CHANGE.HD_TICKET_ID= HD_TICKET.ID and OLD_OWNER.FULL_NAME like 'DefaultTicketOwner'
The error I get is [1054: Unknown column 'HD_TICKET_CHANGE.OWNER_ID' in 'on clause'] when the rule is fired. Is there something I am missing? I know that HD_TICKET_CHANGE.OWNER_ID is no longer there, but I'm unsure on where to look for ownership changed that isn't in the comment. Any help would be appreciated.