I received the following error for plotting a search by
"CreatedMonthly":
ERROR: column reference "created" is ambiguous at character 37
STATEMENT: SELECT COUNT(main.id) AS id, SUBSTR(Created::text,1,7) AS
createdmonthly FROM Tickets main CROSS JOIN Users Users_3 JOIN Groups Groups_1
ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Type = 'Requestor' )
AND ( Groups_1.Instance = main.id ) JOIN CachedGroupMembers
CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (Users_3.EmailAddress ILIKE
'%...@%') AND (main.Status != 'deleted') AND ( ( CachedGroupMembers_2.id IS NOT
NULL ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP BY
SUBSTR(Created::text,1,7)
Here are the applicable lines from lib/RT/Report/Tickets.pm:
sub _FieldToFunction {
my $self = shift;
my %args = (@_);
my $field = $args{'FIELD'};
if ($field =~ /^(.*)(Daily|Monthly|Annually)$/) {
my ($field, $grouping) = ($1, $2);
# Pg 8.3 requires explicit casting
$field .= '::text' if RT->Config->Get('DatabaseType') eq 'Pg';
if ( $grouping =~ /Daily/ ) {
$args{'FUNCTION'} = "SUBSTR($field,1,10)";
}
elsif ( $grouping =~ /Monthly/ ) {
$args{'FUNCTION'} = "SUBSTR($field,1,7)";
}
elsif ( $grouping =~ /Annually/ ) {
$args{'FUNCTION'} = "SUBSTR($field,1,4)";
}
} elsif ( $field =~ /^(?:CF|CustomField)\.{(.*)}$/ ) { #XXX: use CFDecipher
method
...
I added a table specifier "main." to the field to fix the
problem. You may want to do something similar since it looks
like 3.8.8 has the same problem (we are running 3.8.5):
> diff -u lib/RT/Report/Tickets.pm local/lib/RT/Report/Tickets.pm
--- lib/RT/Report/Tickets.pm 2009-10-13 12:24:43.000000000 -0500
+++ local/lib/RT/Report/Tickets.pm 2010-07-14 15:02:59.000000000 -0500
@@ -171,13 +171,13 @@
# Pg 8.3 requires explicit casting
$field .= '::text' if RT->Config->Get('DatabaseType') eq 'Pg';
if ( $grouping =~ /Daily/ ) {
- $args{'FUNCTION'} = "SUBSTR($field,1,10)";
+ $args{'FUNCTION'} = "SUBSTR(main.$field,1,10)";
}
elsif ( $grouping =~ /Monthly/ ) {
- $args{'FUNCTION'} = "SUBSTR($field,1,7)";
+ $args{'FUNCTION'} = "SUBSTR(main.$field,1,7)";
}
elsif ( $grouping =~ /Annually/ ) {
- $args{'FUNCTION'} = "SUBSTR($field,1,4)";
+ $args{'FUNCTION'} = "SUBSTR(main.$field,1,4)";
}
} elsif ( $field =~ /^(?:CF|CustomField)\.{(.*)}$/ ) { #XXX: use
CFDecipher method
my $cf_name = $1;
Regards,
Ken
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
