KACE Product Support

Anyone please?

The error is not cause by a table change. The MariaDB used by the SMA has been updated and is more strict in the SQL syntax checking.

The error message "Column 'CREATED' in field the list is ambiguous" , this indicates that there is more than one table referenced in the SQL statement contains a field called "CREATED".

The SQL statement needs to be update to be more explicit of which of tables that have a CREATED field should be used.

You may want to change "CREATED AS SORT1"  to "HD_TICKET.CREATED AS SORT1"

There is also CREATED in your Order by clause.

"ORDER BY OWNER_NAME ASC, CREATED ASC, TIME_CLOSED DESC, ID ASC"

May require

"ORDER BY OWNER_NAME ASC, HD_TICKET.CREATED ASC, TIME_CLOSED DESC, ID ASC"

I suspect after these changes you may have other errors. So you will need to inform us of any new messages after the updates.

Hi All, 

May I ask assistance since I encounter the similar issue.

Here is the SQL Query:

SELECT HD_TICKET.ID,

HD_TICKET.CREATED, 

S.FULL_NAME AS SUBMITTER_NAME, 

S.USER_NAME AS SUBMITTER_USER_NAME, 

HD_CATEGORY.NAME AS CATEGORY, 

HD_TICKET.TITLE, 

HD_TICKET.RESOLUTION, 

O.FULL_NAME AS OWNER, 

O.USER_NAME AS OWNER_USER_NAME, 

CF0.FULL_NAME AS CO_OWNER_1, 

CF1.FULL_NAME AS CO_OWNER_2, 

HD_TICKET.TIME_OPENED, 

HD_TICKET.TIME_CLOSED, 

IF(HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.IS_PARENT, 

HD_SERVICE_STATUS.NAME, 

HD_STATUS.NAME) AS STATUS, 

ID AS SORT1 FROM HD_TICKET LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) INNER JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) 

LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) 

LEFT JOIN USER CF0 ON (CF0.ID = HD_TICKET.CUSTOM_FIELD_VALUE0) 

LEFT JOIN USER CF1 ON (CF1.ID = HD_TICKET.CUSTOM_FIELD_VALUE1) 

LEFT JOIN HD_SERVICE_STATUS ON HD_TICKET.HD_USE_PROCESS_STATUS 

and HD_TICKET.HD_SERVICE_STATUS_ID and HD_SERVICE_STATUS.ID = 

HD_TICKET.HD_SERVICE_STATUS_ID INNER JOIN HD_STATUS ON 

(HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1)

AND (((date(HD_TICKET.CREATED) >= date_sub(date_sub(curdate(),

interval dayofmonth(curdate()) - 1 day),interval 1 month) 

and date(HD_TICKET.CREATED) < date_sub(curdate(),

interval dayofmonth(curdate()) - 1 day)))

OR (((date(HD_TICKET.TIME_CLOSED) >= date_sub(date_sub(curdate(),

interval dayofmonth(curdate()) - 1 day), interval 1 month) 

and date(HD_TICKET.TIME_CLOSED) < date_sub(curdate(), 

interval dayofmonth(curdate()) - 1 day))))) ORDER BY ID


And here is the error:
mysqli error: [1052: Column 'ID' in field list is ambiguous] in EXECUTE(\n"SELECT HD_TICKET.ID,\nHD_TICKET.CREATED, \nS.FULL_NAME AS SUBMITTER_NAME, \nS.USER_NAME AS SUBMITTER_USER_NAME, \nHD_CATEGORY.NAME AS CATEGORY, \nHD_TICKET.TITLE, \nHD_TICKET.RESOLUTION, \nO.FULL_NAME AS OWNER, \nO.USER_NAME AS OWNER_USER_NAME, \nCF0.FULL_NAME AS CO_OWNER_1, \nCF1.FULL_NAME AS CO_OWNER_2, \nHD_TICKET.TIME_OPENED, \nHD_TICKET.TIME_CLOSED, \nIF(HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.IS_PARENT, \nHD_SERVICE_STATUS.NAME, \nHD_STATUS.NAME) AS STATUS, \nID AS SORT1 FROM HD_TICKET LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) INNER JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) \nLEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) \nLEFT JOIN USER CF0 ON (CF0.ID = HD_TICKET.CUSTOM_FIELD_VALUE0) \nLEFT JOIN USER CF1 ON (CF1.ID = HD_TICKET.CUSTOM_FIELD_VALUE1) \nLEFT JOIN HD_SERVICE_STATUS ON HD_TICKET.HD_USE_PROCESS_STATUS \nand HD_TICKET.HD_SERVICE_STATUS_ID and HD_SERVICE_STATUS.ID = \nHD_TICKET.HD_SERVICE_STATUS_ID INNER JOIN HD_STATUS ON \n(HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1)\nAND (((date(HD_TICKET.CREATED) >= date_sub(date_sub(curdate(),\ninterval dayofmonth(curdate()) - 1 day),interval 1 month) \nand date(HD_TICKET.CREATED) < date_sub(curdate(),\ninterval dayofmonth(curdate()) - 1 day)))\nOR (((date(HD_TICKET.TIME_CLOSED) >= date_sub(date_sub(curdate(),\ninterval dayofmonth(curdate()) - 1 day), interval 1 month) \nand date(HD_TICKET.TIME_CLOSED) < date_sub(curdate(), \ninterval dayofmonth(curdate()) - 1 day))))) ORDER BY ID LIMIT 0")