I am a newbie when it comes to sql so please bear that in mind here.  With the help of ITNINJA and all of its bloggers I have successfully set up several CTR's to pass information from a parent ticket to a single child ticket.  However, when I introduce more than one child ticket I get errors in the sql select showing multiple ticket ID's and not updating anything in the child tickets.  What am I doing wrong here?  I thought that  if I mirrored the CTR from one child ticket in another child ticket in the same process it would move the same information over.

My scenario is this:  Multiple queue's (which are named departments) that are set up strictly for a new hire process.  A manager will submit a process which is in the HR Queue for the new hire.  I then created child ticket for this in every other department (accounting, IT, marketing and so on).  As I stated before, I got this to work when there was only one child ticket but as soon as a second one is introduced there was no update.

Thanks for your help in advance!

 

OK here is my select query:

SELECT PARENT.TITLE AS 'Parent Title', HD_TICKET.TITLE AS 'Child Title', HD_TICKET.ID AS 'ID'
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID

and the update query:

UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.TITLE = PARENT.TITLE
WHERE ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)

This works when there is only ONE child ticket in the process.  when I introduce TWO or more child tickets to a process I get SQL errors.

 

Please HELP 

1 Comment   [ + ] Show Comment

Comments

  • Here is what I found to work. Hopefully this will help someone in the future.
    Select query:
    SELECT
    CHILD.ID AS 'ID'
    FROM HD_TICKET CHILD Inner Join
    HD_TICKET_CHANGE CHILD_CHANGE On CHILD_CHANGE.HD_TICKET_ID = CHILD.ID
    WHERE PARENT_ID != 0 AND CHILD_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%'

    update query:
    UPDATE HD_TICKET PARENT Inner Join
    HD_TICKET_CHANGE PARENT_CHANGE On PARENT_CHANGE.HD_TICKET_ID = PARENT.ID , HD_TICKET CHILD Inner Join
    HD_TICKET_CHANGE CHILD_CHANGE On CHILD_CHANGE.HD_TICKET_ID = CHILD.ID
    SET CHILD_CHANGE.COMMENT = PARENT_CHANGE.COMMENT
    WHERE ((PARENT.ID = CHILD.PARENT_ID) AND CHILD.ID IN (<TICKET_IDS>))
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity