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")
Please log in to answer
Posted by: jmarotto 6 years ago