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 ‘%ktm@%’) 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)";
-
} elsif ( $field =~ /^(?:CF|CustomField).{(.*)}$/ ) { #XXX: use CFDecipher method$args{'FUNCTION'} = "SUBSTR(main.$field,1,4)"; }
my $cf_name = $1;
Regards,
Ken