Create report option causes sql syntax error
So we are using the built in feather in the ticket system to create a report, and not matter what we do it causes a syntax error with sql. Kace is doing their norm and saying this is a custom request and they can work on it for 250/hr. I for one do not see how if their product generates a syntax that does not work in what way is this not a software bug on their end, and how does it become my responsibility to fix it, but none the less here is the syntax error. any guru's on here know how to fix it? It works in other parts of Kace, such as inventory, but it fails on the help desk section of the appliance.
mysql error: [1054: Unknown column 'SUBMITTER_NAME' in 'order clause'] in EXECUTE( "select CONCAT('TICK:',HD_TICKET.ID) as 'Number' ,DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as 'Created' ,if((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as 'Owner / Assigned To' ,if((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as 'Submitter' ,HD_TICKET.CUSTOM_FIELD_VALUE0 as 'Requested Priority' ,HD_PRIORITY.NAME as 'Priority' ,HD_STATUS.NAME as 'Status' ,HD_TICKET.DUE_DATE as 'Due Date' ,HD_TICKET.TITLE as 'Summary Description' from (HD_TICKET, HD_STATUS, HD_IMPACT, HD_CATEGORY) LEFT JOIN HD_PRIORITY on (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID where (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID and HD_TICKET.HD_QUEUE_ID = 9 and HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID and HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) and STATE in ('opened', 'stalled') order by SUBMITTER_NAME asc LIMIT 0")
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
jmarotto
10 years ago
This is happening because the ORDER BY is trying to list the results by a column that doesn't exist in the query. You can remove the "_NAME" from ODER BY SUBMITTER_NAME so it reads ORDER BY SUBMITTER ASC. I also had to remove the LIMIT 0 to have any query results returned.
