Dear RT users/developpers 
I’m trying to draw charts grouped by customfield (using the
functionnality at the bottom of a search result) and on a customfiled of
type “select unique value”. But the result graph is empty 
It works with other group by like status, queue, …
If someone has some hints on solving this … thank you!
Dear RT users/developpers
I’m trying to draw charts grouped by customfield (using the
functionnality at the bottom of a search result) and on a customfiled of
type “select unique value”. But the result graph is empty
It works with other group by like status, queue, …If someone has some hints on solving this … thank you!
I forgot … I’m using the latest RT 3.6.5 on RH CentOS 5 with Oracle XE
10.
what shows up in your error logs?------- Original message -------
From: Emmanuel Lacour elacour@easter-eggs.com
Sent: 10-10-'07, 3:43
Dear RT users/developpers
I’m trying to draw charts grouped by customfield (using the
functionnality at the bottom of a search result) and on a customfiled of
type “select unique value”. But the result graph is empty
It works with other group by like status, queue, …If someone has some hints on solving this … thank you!
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.comDiscover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Hi, dear RT primary Author 
what shows up in your error logs?
That’s the good question … sorry I forgot to look here 
There is DBD prepare errors:
RT: RT::Handle=HASH(0x8134df1c) couldn’t prepare the query '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
'ORA-00904: “OBJECTCUSTOMFIELDVALUES_1”.“CONTENT”: invalid identifier
(DBD ERROR: error possibly near <*> indicator at char 596 in 'SELECT COUNT(main.id) AS id, ObjectCustomFieldValues_1.Content AS col1 FROM ( SELECT DISTINCT main.id FROM Tickets main LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1
Here is more information on my setup:
DBIx-SearchBuilder-1.49
DBD-Oracle-1.19
Oracle Xe 10.2.0.1
Perl 5.8.8
Thanks for your help 
Hi, dear RT primary Author
what shows up in your error logs?
That’s the good question … sorry I forgot to look here
I suspect that it has to do with “this was tested on mysql and pg”
Any oracle folks see what we’re doing wrong with that SQL?
PGP.sig (186 Bytes)
I suspect that it has to do with “this was tested on mysql and pg”
if only I had power to choose the database Engine …
distinctquery, Tickets main WHERE (main.id = distinctquery.id)
Shouldn’t be present a comma between Tickets and main?
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
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
;
Jesse Vincent wrote:
I suspect that it has to do with “this was tested on mysql and pg”
Any oracle folks see what we’re doing wrong with that SQL?There is DBD prepare errors:
RT: RT::Handle=HASH(0x8134df1c) couldn’t prepare the query '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
Problem is that in the group by there is a alias which is defined in the
subquery.
ObjectCustomFieldValues_1 is only known in the innermost query not the
outer one.
There is another problem aswell since modifing the query gives me
another unidentified error.
I’ll see if my DBA can have a look at it.
Joop
Emmanuel Lacour wrote:> 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 ;
Weird, this one works without modification on Oracle XE.
I suspect that it has to do with “this was tested on mysql and pg”
if only I had power to choose the database Engine …
It should work on everything. The fact that it doesn’t is definitely
a bug.
PGP.sig (186 Bytes)
To all,
We are on Oracle 10g and we have the same problems with custom fields
when it comes to charts from query results or sorting by them as well.
Kenn
LBNLOn 10/10/2007 1:25 PM, Jesse Vincent wrote:
On Oct 10, 2007, at 10:03 AM, Emmanuel Lacour wrote:
On Wed, Oct 10, 2007 at 09:42:21AM -0400, Jesse Vincent wrote:
I suspect that it has to do with “this was tested on mysql and pg”
if only I had power to choose the database Engine …
It should work on everything. The fact that it doesn’t is definitely a bug.
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.comDiscover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
It’s an experimental and untested patch that may help.
When a query has a “group by” part we really don’t want to make it
distinct as I don’t see any way it can have not unique entries in the
result set.
Patch is for DBIx::SearchBuilder, to try it use the following steps:
Emmanuel Lacour wrote:
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 ;
Weird, this one works without modification on Oracle XE.
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.comDiscover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Best regards, Ruslan.
dbix-sb-oracle.patch (609 Bytes)
It’s an experimental and untested patch that may help.
It helps, and works here :))
Thanks you very much!
It’s an experimental and untested patch that may help.
It helps, and works here :))
Thanks you very much!
That’s cool, let it spin for several days… And may be during next
week we’ll release new version of SB.
Joop, any comments?
Best regards, Ruslan.
Ruslan Zakirov wrote:> On 10/11/07, Emmanuel Lacour elacour@easter-eggs.com wrote:
On Thu, Oct 11, 2007 at 06:44:04AM +0400, Ruslan Zakirov wrote:
It’s an experimental and untested patch that may help.
It helps, and works here :))
Thanks you very much!
That’s cool, let it spin for several days… And may be during next
week we’ll release new version of SB.Joop, any comments?
Haven’t had time to try the patch on our test instance and don’t know
when I will. Might be somewhere next week.
The patch looks clean to me and if that statement that worked is the
result of this patch then it looks certainly OK.
Joop
The patch is in DBIx::SearchBuilder 1.50 which on its way to the CPAN.
It will be available from mirrors soon.On Oct 11, 2007 11:08 PM, Joop van de Wege JoopvandeWege@mococo.nl wrote:
Ruslan Zakirov wrote:
On 10/11/07, Emmanuel Lacour elacour@easter-eggs.com wrote:
On Thu, Oct 11, 2007 at 06:44:04AM +0400, Ruslan Zakirov wrote:
It’s an experimental and untested patch that may help.
It helps, and works here :))
Thanks you very much!
That’s cool, let it spin for several days… And may be during next
week we’ll release new version of SB.Joop, any comments?
Haven’t had time to try the patch on our test instance and don’t know
when I will. Might be somewhere next week.
The patch looks clean to me and if that statement that worked is the
result of this patch then it looks certainly OK.Joop
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.comDiscover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Best regards, Ruslan.
The patch is in DBIx::SearchBuilder 1.50 which on its way to the CPAN.
It will be available from mirrors soon.
Great, no problem here with this patch.
Emmanuel Lacour ------------------------------------ Easter-eggs
44-46 rue de l’Ouest - 75014 Paris - France - M�tro Gait�
Phone: +33 (0) 1 43 35 00 37 - Fax: +33 (0) 1 41 35 00 76
mailto:elacour@easter-eggs.com - http://www.easter-eggs.com