Miscellaneous

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 -

Any suggestions would be appreciated. Thanks