KACE Product Support

Please post your select statement so we can see if there are other errors in it. - chucksteel 10 years ago

Please post your select - brunocardoso 10 years ago

SELECT

-- ticket fields

HD_TICKET.ID, -- $id

HD_TICKET.ID AS TICKNUM, -- $ticknum

HD_TICKET.TITLE, -- $title

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

-- change fields

C.COMMENT, -- $comment

C.DESCRIPTION, -- $description

GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',

H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')

ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history

-- about the updater

UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname

UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname

UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email

IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional

-- about the owner

OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname

OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname

OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email

IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user

-- about the submitter

SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname

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 CATEGORY, -- $category

-- other fields

-- -- example of static distribution list

'helpdesk@mycompany.com' 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>

/* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID

/* 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

/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID

WHERE

C.DESCRIPTION LIKE 'TICKET CREATED%'

/* this is necessary when using group by functions */

GROUP BY HD_TICKET.ID

HAVING 1=1 - jharrell 10 years ago

I assume the table name has changed in 5.5, which is the version i'm on. - jharrell 10 years ago

I"m still very new to SQL. Does this part look correct? It is the section that errors.

/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID

AND C.ID=<CHANGE_ID> - jharrell 10 years ago

Hmm.. should i open a ticket with KACE Support on this one? - jharrell 10 years ago