Want to have a KACE custom report. using Custom_1 and Custom_2 from the User table
I'm trying to base a report off the Open Tickets by Owner model, but instead of owner I'm using submitter.
Everything looks ok until I get into trying to filter the report based on the contents of Custom_1 and Custom_2, then it fails out with an error "mysql error: [1146: Table 'ORG1.submitter' doesn't exist]". When I remove the offending filter from the Where clause it works. I realize I'm missing something obvious here, I just can't see it.
Here's the current query:
select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE,
CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_OPEN,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME,
(select CUSTOM_2 from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_Company,
(select CUSTOM_1 from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_CostCenter
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
left join submitter ON USER.ID=HD_TICKET.SUBMITTER_ID
where HD_STATUS.STATE = 'opened' and
(
(submitter.Custom_2 = '070' and submitter.Custom_1 = '0102')
)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
Answers (1)
Users are stored in the USER table, there isn't a submitter table. If you want to alias the join on the users table as submitter, then change your join to:
LEFT JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID
The custom fields will now be available as SUBMITTER.CUSTOM_2, etc.
Comments:
-
Perfect. Part of my problem was also that I didn't have the table name capitalized. - jholmes 12 years ago
-
I don't know if it strictly needs to be capitalized, that's just a convention that I use. - chucksteel 12 years ago