/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 (2)

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

Anyone please?

Posted by: KevinG 2 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 2 months ago
  • Glad to hear that your issue is resolved. :-) - KevinG 2 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