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


Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

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.

Answered 04/24/2012 by: chucksteel
Red Belt

  • Perfect. Part of my problem was also that I didn't have the table name capitalized.
  • I don't know if it strictly needs to be capitalized, that's just a convention that I use.
Please log in to comment
Answer this question or Comment on this question for clarity

Share