I'm trying to append my tickets category to the title of the ticket when a user submits a new ticket. Because The category referes to a number, which references another tables values I have to use query varibles in my update command. My code is below. My error is  

__________ERROR______________

09/11/2013 16:00:51> Starting: 09/11/2013 16:00:51

09/11/2013 16:00:51> Executing Select Query...

09/11/2013 16:00:51> selected 1 rows

09/11/2013 16:00:51> Executing Update Query...

09/11/2013 16:00:51> 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 '@CAT := (SELECT HD_CATEGORY_ID From HD_TICKET WHERE ID := 4123),

@CATname := (SE' at line 2] in EXECUTE("Update HD_TICKET

   SET @CAT := (SELECT HD_CATEGORY_ID From HD_TICKET WHERE ID := 4123),

@CATname := (SELECT NAME FROM HD_CATEGORY WHERE ID := @CAT),

HD_TICKET.TITLE := @CATname;

  where 

(HD_TICKET.ID in (4123))

")

 

09/11/2013 16:00:51> Ending: 09/11/2013 16:00:51"

____END OF ERROR____

Code:

Update HD_TICKET

   SET @CAT := (SELECT HD_CATEGORY_ID From HD_TICKET WHERE ID := <TICKET_IDS>),

@CATname := (SELECT NAME FROM HD_CATEGORY WHERE ID := @CAT),

HD_TICKET.TITLE := @CATname;

  where 

(HD_TICKET.ID in (<TICKET_IDS>))

 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

 

 

***SEE OTHER POST, THIS WAS INITIAL THINKING POST***

 

From what I can see, you might be overcomplicating it :)

so what you really need is to concat the category to the title looking SIMILAR TO

 update HD_TICKET
join HD_CATEGORY on HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
set HD_TICKET.TITLE = concat(HD_TICKET.TITLE, " : ", HD_CATEGORY.NAME)

I just tested that and it worked great, on a single ticket, but testing it ON TICKET SAVE has come up with some interesting results, so please make sure you test in a test environment.


What you can do though, is set it up sorta like yours using the TICKET_IDS in the update query with the query I supplied, and using a rule that only finds tickets on creations.  I will build that and report back in.


I ran it within the select query section of a new rule (literally just copy/pasted it in) and then did a run now.  It says it didn't select anything (because it's not a select query) but a check of your ticket shows that it should've worked.

 

 

 

Answered 09/12/2013 by: Wildwolfay
Red Belt

Please log in to comment
1

Okay what I described secondly worked great:

here is my SELECT QUERY:

 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,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
COMMENT
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>
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
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_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%'

 

then the update query:

  update HD_TICKET
join HD_CATEGORY on HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
set HD_TICKET.TITLE = concat(HD_TICKET.TITLE, " : ", HD_CATEGORY.NAME)
WHERE (HD_TICKET.ID in (<TICKET_IDS>))

and this seemed to work great on the 5 or so tickets I created and played with.

 (note:  Set it for ticket save!)

FYI This is a good idea, which is why I jumped on this, good on you !

 

Give this a try and let me know.

Answered 09/12/2013 by: Wildwolfay
Red Belt

  • Wow, this worked awesome thanks Wildwolfay! This was actually my first post on ITNinja and I'm really impressed how quickly and correctly my question was answered. Seems like a great community!
    • This place is amazing, only been here about 5-6 months (maybe?) and I've learned a TON, as I'm super entry-level IT.

      If you are keen on just 'learning stuff' the blogs are great, especially ones done by John Verboski

      (here's a link to one: http://www.itninja.com/blog/view/k1000-labels-effective-organization-process-flow-using-manual-smart-ldap-labels-and-label-groups )

      He has a great SQL primer as well, check out his posts.

      Glad I was able to actually give back a little :)
Please log in to comment
Answer this question or Comment on this question for clarity