On Wed, Oct 10, 2007 at 05:46:31PM +0200, Gianluca Cecchi wrote: > Sorry main was an alias for Tickets table.... > > If I use the same query on mysql 9without the comma...) I get: > > ERROR 1054 (42S22): Unknown column 'ObjectCustomFieldValues_1.Content' in > 'field list' > > And the oracle error 904 is "invalid column name" too >
Here is the SQL for the same Graph on a MySQL RT, gived by StatementLog (graph by CustomFields works with MySQL): SELECT DISTINCT COUNT(main.id) AS id, ObjectCustomFieldValues_1.Content AS col1 FROM (Tickets main LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ((ObjectCustomFieldValues_1.CustomField = '1')) AND ( (ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket')) AND ( (ObjectCustomFieldValues_1.Disabled = '0')) AND ( ObjectCustomFieldValues_1.ObjectId = main.id)) WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ((main.Created < '2007-10-31 23:00:00') AND (main.Created > '2007-09-29 22:00:00')) GROUP BY ObjectCustomFieldValues_1.Content ; and the one for the Oracle RT: SELECT COUNT(main.id) AS id, ObjectCustomFieldValues_1.Content AS col1 FROM ( SELECT DISTINCT main.id FROM Tickets main LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( ObjectCustomFieldValues_1.CustomField = '1' ) AND ( ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket' ) AND ( ObjectCustomFieldValues_1.Disabled = '0' ) AND ( ObjectCustomFieldValues_1.ObjectId = main.id ) WHERE (main.Status != 'deleted') AND (main.Status != 'resolved') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) ) distinctquery, Tickets main WHERE (main.id = distinctquery.id) GROUP BY ObjectCustomFieldValues_1.Content ; _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
