/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


how to add a field to the following report

03/31/2017 781 views
Here is a part of my existing report and I need to add the HD_CATEGORY_NAME field to it. If I use that field name as stated, I get a SQL error. Any help would be appreciated.

SELECT HD_TICKET.ID AS Ticket_ID,

 HD_TICKET.TITLE AS Issue_Summary,

 HD_TICKET.CUSTOM_FIELD_VALUE4 AS Department,

 S.FULL_NAME AS Customer,

O.FULL_NAME AS Assigned_To,

 HD_TICKET.CREATED,

HD_TICKET.TIME_CLOSED, HD_STATUS.NAME AS Status,HD_TICKET.CUSTOM_FIELD_VALUE1 AS Work_Effort_Estimate,

 HD_TICKET.CUSTOM_FIELD_VALUE2 AS Severity

 FROM HD_TICKET  JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)

 LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)

 LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)

 JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)

 JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID

 WHERE (HD_TICKET.HD_QUEUE_ID = 1)

 AND ((HD_TICKET.CREATED > '2014-12-31 23:59:59')

 AND (HD_TICKET.TIME_CLOSED > '2014-12-31 23:59:59'))


Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

2
The HD_TICKET table only stores the category ID and the full details for each category is located in another table called "HD_CATEGORY". So we'll need to add that table (i.e. join it) and add one of its columns to our SELECT clause.

Add this JOIN clause:
JOIN HD_CATEGORY on (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)

And add this to your select columns: 
HD_CATEGORY.NAME AS Category
Answered 03/31/2017 by: JasonEgg
Red Belt

  • Worked perfectly, thank you very much. You made life a little easier today.

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

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ