John, Thanks for the reply.
I honestly have no idea what would be the best way (or the correct way for that matter) to do this query (my specialty is actually in networking, not database engineering). The problem is simply that out of the box, 2 of the 3 reports fail to produce any results (Resolved by owner and Resolved in date range). The error generated in the RT log (see below) is all I really have that shows the problem. I can make a change to the affected file, but need some guidance in finding where/how to make this change. Attempting to run both of these reports produces the following set of errors: [Thu Apr 16 19:11:17 2009] [warning]: DBD::Oracle::db prepare failed: ORA-00979: not a GROUP BY expression (DBD ERROR: error possibly near <*> indicator at char 29 in 'SELECT COUNT(main.id) AS id, <*>Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name ') [for Statement "SELECT COUNT(main.id) AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name "] at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 465, <GEN59> line 85. (/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:465) [Thu Apr 16 19:11:17 2009] [warning]: RT::Handle=HASH(0x2b03abb5bfb0) couldn't prepare the query 'SELECT COUNT(main.id) AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name 'ORA-00979: not a GROUP BY expression (DBD ERROR: error possibly near <*> indicator at char 29 in 'SELECT COUNT(main.id) AS id, <*>Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name ') (/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:472) [Thu Apr 16 19:11:18 2009] [warning]: DBD::Oracle::db prepare failed: ORA-00979: not a GROUP BY expression (DBD ERROR: error possibly near <*> indicator at char 29 in 'SELECT COUNT(main.id) AS id, <*>Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name ') [for Statement "SELECT COUNT(main.id) AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name "] at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 465, <GEN61> line 85. (/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:465) [Thu Apr 16 19:11:18 2009] [warning]: RT::Handle=HASH(0x2b03abb5bfb0) couldn't prepare the query 'SELECT COUNT(main.id) AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name 'ORA-00979: not a GROUP BY expression (DBD ERROR: error possibly near <*> indicator at char 29 in 'SELECT COUNT(main.id) AS id, <*>Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name ') (/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:472) [Thu Apr 16 19:11:29 2009] [warning]: DBD::Oracle::db prepare failed: ORA-00979: not a GROUP BY expression (DBD ERROR: error possibly near <*> indicator at char 29 in 'SELECT COUNT(main.id) AS id, <*>Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name ') [for Statement "SELECT COUNT(main.id) AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name "] at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 465, <GEN67> line 85. (/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:465) [Thu Apr 16 19:11:29 2009] [warning]: RT::Handle=HASH(0x2b03abb5bfb0) couldn't prepare the query 'SELECT COUNT(main.id) AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name 'ORA-00979: not a GROUP BY expression (DBD ERROR: error possibly near <*> indicator at char 29 in 'SELECT COUNT(main.id) AS id, <*>Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name ') (/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:472) [Thu Apr 16 19:11:29 2009] [warning]: DBD::Oracle::db prepare failed: ORA-00979: not a GROUP BY expression (DBD ERROR: error possibly near <*> indicator at char 29 in 'SELECT COUNT(main.id) AS id, <*>Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name ') [for Statement "SELECT COUNT(main.id) AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name "] at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 465, <GEN69> line 85. (/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:465) [Thu Apr 16 19:11:29 2009] [warning]: RT::Handle=HASH(0x2b03abb5bfb0) couldn't prepare the query 'SELECT COUNT(main.id) AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name 'ORA-00979: not a GROUP BY expression (DBD ERROR: error possibly near <*> indicator at char 29 in 'SELECT COUNT(main.id) AS id, <*>Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY Users_1.Name ') (/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:472) Thanks again for your assistance. -CK -----Original Message----- From: John Hascall [mailto:[email protected]] Sent: Thursday, April 16, 2009 9:54 AM To: Charles Kugelman Cc: [email protected] Subject: Re: [rt-users] Report "Resolved by owner" fails to output, generates error in log > SELECT COUNT(main.id) AS id, > Users_2.Name AS col1 > FROM Tickets main > LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) > LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) > WHERE (main.Status != 'deleted') > AND (main.Status = 'resolved' AND main.Queue = '5') > AND (main.Type = 'ticket') > AND (main.EffectiveId = main.id) > GROUP BY Users_2.Name; While we are looking at this query, it seems to me that the first where clause is made redundant by the second one: > WHERE (main.Status != 'deleted') > AND (main.Status = 'resolved' AND main.Queue = '5') If (main.Status = 'resolved' AND main.Queue = '5') then it must be true that (main.Status != 'deleted') And I don't see any use of the Tickets table. Also, where is the 2nd left join used? Thus: > SELECT COUNT(main.id) AS id, > Users.Name AS col1 > FROM main > LEFT JOIN Users ON ( Users.id = main.Owner ) > WHERE (main.Status = 'resolved') > AND (main.Queue = '5') > AND (main.Type = 'ticket') > AND (main.EffectiveId = main.id) > GROUP BY Users.Name; would seem to be a lot simpler. Or have I overlooked something obvious? John _______________________________________________ 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
