/build/static/layout/Breadcrumb_cap_w.png

Kace SMA table changes - Please help

I had a closed ticket report that has been working all these years but is no longer working since upgrading to ver 13. I am not sure what tables changed and I am not good with sql. Can someone please take a look?

Here is the sql.

SELECT O.FULL_NAME AS OWNER_NAME,HD_TICKET.ID,HD_CATEGORY.NAME AS CATEGORY,HD_TICKET.TITLE,GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED,HD_TICKET.CREATED,HD_TICKET.TIME_CLOSED,S.FULL_NAME AS SUBMITTER_NAME FROM HD_TICKET  LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW() AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY)))  GROUP BY HD_TICKET.ID ORDER BY OWNER_NAME,CREATED asc,TIME_CLOSED desc,ID

And here is the error.

mysqli error: [1052: Column 'CREATED' in field list is ambiguous] in EXECUTE("DESCRIBE SELECT O.FULL_NAME AS OWNER_NAME, HD_TICKET.ID, HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.TITLE, GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED, S.FULL_NAME AS SUBMITTER_NAME, CREATED AS SORT1, TIME_CLOSED AS SORT2, ID AS SORT3 FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) INNER JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW() AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) GROUP BY HD_TICKET.ID ORDER BY OWNER_NAME ASC, CREATED ASC, TIME_CLOSED DESC, ID ASC")


0 Comments   [ + ] Show comments

Answers (3)

Answer Summary:
Posted by: bozadmin 10 months ago
Brown Belt
0

Anyone please?

Posted by: KevinG 10 months ago
Red Belt
0

Top Answer

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.


Comments:
  • Thank you so much for pointing me in the right direction. After changing it to HD_TICKET.CREATED AS TICKET_CREATED and a couple of other changes, it's working again. - bozadmin 10 months ago
  • Glad to hear that your issue is resolved. :-) - KevinG 10 months ago
Posted by: christian.salvador 8 months ago
White Belt
0

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")


Comments:
  • "mysqli error: [1052: Column 'ID' in field list is ambiguous] "

    In the last line of your SQL statement, change ORDER BY ID to ORDER BY HD_TICKET.ID - KevinG 8 months ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ