/build/static/layout/Breadcrumb_cap_w.png
09/13/2018 176 views
Evening folks,

This query returns everything I expect -

SELECT
        -- ticket fields
      HD_TICKET.ID AS TICKNUM, -- $ticknum
      HD_TICKET.TITLE AS BRIEF_DESC, -- $title
      HD_TICKET.CUSTOM_FIELD_VALUE19 AS AIRLINE, -- $airline
      DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
      DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
        -- mcs airline asset fields   
      AL.NAME AS AIRLINE_ASSET,
      AD14.FIELD_10034 AS AIRLINE_REG, -- $region
        -- mcs preferred focal asset fields
      A10061.NAME AS PREF_CONT, -- $pref_cont
      AD17.FIELD_10051 AS PC_EMAIL, -- $pc_email
      PC.ID AS PREF_CONT_ID,   
        -- change fields
      C.COMMENT, -- $comment
      C.DESCRIPTION, -- $description
        -- about the owner
      OWNER.ID, -- $owner_id
        -- about the submitter
      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
      SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
        -- about priority
      P.NAME AS PRIORITY, -- $priority
        -- about status
      S.NAME AS STATUS,   -- $status
         -- about impact
      I.NAME AS IMPACT,   -- $impact
        -- about category
      CAT.NAME AS CYCLE, -- $cycle
        -- other fields
        -- -- example of static distribution list
    'media.field.report@panasonic.aero' AS NEWTICKETEMAIL -- $newticketemail
    FROM HD_TICKET
     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID = <CHANGE_ID>
     /*airline asset ID**/ JOIN ASSET AL ON AL.NAME = HD_TICKET.CUSTOM_FIELD_VALUE19
     /* airline data*****/ JOIN ASSET_DATA_14 AD14 ON AL.ASSET_DATA_ID = AD14.ID AND AL.ASSET_TYPE_ID=14
     /* assoc asset tbl */ JOIN ASSET_ASSOCIATION AX10061 ON AX10061.ASSET_ID = AL.ID AND AX10061.ASSET_FIELD_ID=10061 /*Airline*/

     /*associ pref focal**/ LEFT JOIN ASSET_ASSOCIATION J10061 ON J10061.ASSET_ID = AL.ID AND J10061.ASSET_FIELD_ID=10061 /*Airline Preferred Contact*/
     /*pref focal asst id*/ LEFT JOIN ASSET A10061 ON A10061.ID = J10061.ASSOCIATED_ASSET_ID
     /*pref focal data****/ LEFT JOIN ASSET_DATA_17 AD17 ON A10061.ASSET_DATA_ID = AD17.ID
     /*pre focal user ID**/ LEFT JOIN USER PC ON A10061.NAME = PC.FULL_NAME

     /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
     /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
     /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
     /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
     /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
     /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
    WHERE HD_TICKET.HD_QUEUE_ID=41
    and C.DESCRIPTION LIKE 'TICKET CREATED%'
    and S.ID =331   
    and HD_TICKET.CUSTOM_FIELD_VALUE19 !=''
      /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1

The update fails, consistently, with syntax errors "near '))" Because I've tried every possible combination of WHERE (T.ID in (<TICKET_IDS>)) I'm guessing the kbox is unable to tell me the actual problem with -

UPDATE HD_TICKET T
    JOIN ASSET AL ON AL.NAME = T.CUSTOM_FIELD_VALUE19
    JOIN ASSET_ASSOCIATION J10061 ON J10061.ASSET_ID = AL.ID AND J10061.ASSET_FIELD_ID=10061
    JOIN ASSET A10061 ON A10061.ID = J10061.ASSOCIATED_ASSET_ID
    JOIN ASSET_DATA_17 AD17 ON A10061.ASSET_DATA_ID = AD17.ID
    JOIN USER PC ON A10061.NAME = PC.FULL_NAME
SET T.OWNER_ID = PC.ID
    WHERE (T.ID in (<TICKET_IDS>))
AND T.HD_QUEUE_ID=41

I'm trying to set the OWNER_ID in a new ticket. The select query returns all of the proper information using the same joins as the update. I can make minor changes to the update, converting it to a select, and that query returns what I expect.

Here is the log run error -

09/13/2018 18:02:00> Starting: 09/13/2018 18:02:00 09/13/2018 18:02:00> Executing Select Query... 09/13/2018 18:02:00> selected 1 rows 09/13/2018 18:02:00> Executing Update Query... 09/13/2018 18:02:00> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) AND PC.FULL_NAME=A10061.NAME AND T.HD_QUEUE_ID=41' at line 8] in EXECUTE("UPDATE HD_TICKET T JOIN ASSET AL ON AL.NAME = T.CUSTOM_FIELD_VALUE19 JOIN ASSET_ASSOCIATION J10061 ON J10061.ASSET_ID = AL.ID AND J10061.ASSET_FIELD_ID=10061 JOIN ASSET A10061 ON A10061.ID = J10061.ASSOCIATED_ASSET_ID JOIN ASSET_DATA_17 AD17 ON A10061.ASSET_DATA_ID = AD17.ID JOIN USER PC ON A10061.NAME = PC.FULL_NAME SET T.OWNER_ID = PC.ID WHERE (T.ID in ()) AND PC.FULL_NAME=A10061.NAME AND T.HD_QUEUE_ID=41") 09/13/2018 18:02:00> Ending: 09/13/2018 18:02:00


Any suggestions would be appreciated. Thanks
Answer Summary:
2 Comments   [ + ] Show comments

Comments

  • The error says
    (T.ID in ())
    This seems to me to say it's not returning anything for TICKET_IDS.
    Is this rule run on Ticket Save?
  • Yes, and you can see in the run log the Select grabs the target. I use a very similar query, in the same queue, to email the person I'm trying to set as the owner. It functions quite well.
    • This is a shot in the dark, because I'm no SQL expert, but you join the User table in both the Select and Update with the alias PC. What if you change one of those to PC1 or something?

Answer Chosen by the Author

2
Your select query doesn't include an ID column. The appliance will create the <TICKET_IDS> variable from the values contained in that column, so if you don't have one in the select statement, it doesn't generate the variable.

Answered 09/14/2018 by: chucksteel
Red Belt

  • Actually it does, its just aliased as TICKNUM. (first line in the select) But since I am truly unable to move forward with this I just added an additional line w/o the alias. The same syntax error is returned when I run it. sigh*
    • The column name is what matters, so the alias breaks the creation of the variable. You are also selecting OWNER.ID, which might be causing a conflict, you might want to alias that one to make sure.
      • 09/14/2018 12:17:02> Starting: 09/14/2018 12:17:02 09/14/2018 12:17:02> Executing Select Query... 09/14/2018 12:17:02> selected 1 rows 09/14/2018 12:17:02> Executing Update Query... 09/14/2018 12:17:02> updated 1 rows 09/14/2018 12:17:02> Ending: 09/14/2018 12:17:02
  • Once again, I kneel before your greatness. Thanks so much for the assist. Truly made my Friday :)

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share