Hello,

I'm looking for a possibility to use the name from the associated asset in the parent ticket for the child ticket.
I have just started with SQL scripting - hope somebody can help me..

In the ticket rule the SQL select works - the ticket rule give 2 rows back - the follow code row doesn't work and I don't know why.

HD.TICKET.TITLE = (SELECT NAME FROM ORG1.ASSET WHERE ORG1.ASSET.ID =
                  (SELECT ASSET_ID FROM HD_TICKET WHERE HD_TICKET.ID = PARENT_ID))


Thank you!

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • The error message I get is:
    'mysqli error: [1093: Table 'HD_TICKET' is specified twice, both as a target for 'UPDATE' and as a separate source for data]'
Please log in to comment

Answer Chosen by the Author

1
I have a Solution!

Here the whole SQL-update

update HD_TICKET as T
  join HD_STATUS as T5
    on T.HD_QUEUE_ID = T5.HD_QUEUE_ID
  left join HD_TICKET as PARENT
    on PARENT.ID = T.PARENT_ID
  left join ORG1.ASSET as A
    on A.ID = PARENT.ASSET_ID
set
  T.HD_STATUS_ID = T5.ID,
  T.TIME_OPENED  = IF(T5.STATE = 'opened', NOW(), T.TIME_OPENED),
  T.TIME_CLOSED  = IF(T5.STATE = 'closed', NOW(), T.TIME_CLOSED),
  T.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), T.TIME_STALLED),
  T.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_RATING),
  T.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_COMMENT),
  T.TITLE = A.NAME
where T5.NAME = 'Geplant' 
  and T.ID in (<TICKET_IDS>) ;

The SQL-update change the status from new to planed ('Geplant') and change the childticket title to the name from the associated asset in the parentticket.

Answered 09/05/2016 by: svmay
Sixth Degree Black Belt

Please log in to comment

Answers

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