/build/static/layout/Breadcrumb_cap_w.png
03/31/2017 717 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.