R: [rt-users] Chart grouped by customfield

[Gianluca Cecchi]

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

> -----Messaggio originale-----
> Da: rt-users-bounces at lists.bestpractical.com 
> [mailto:rt-users-bounces at lists.bestpractical.com] Per conto 
> di Gianluca Cecchi
> Inviato: mercoledì 10 ottobre 2007 17.40
> A: Jesse Vincent; Emmanuel Lacour
> Cc: rt-users at lists.bestpractical.com
> Oggetto: R: [rt-users] Chart grouped by customfield
> 
> 
> > distinctquery, Tickets main WHERE (main.id = distinctquery.id)
> 
> Shouldn't be present a comma between Tickets and main? 
> 
> > -----Messaggio originale-----
> > Da: rt-users-bounces at lists.bestpractical.com
> > [mailto:rt-users-bounces at lists.bestpractical.com] Per conto 
> di Jesse 
> > Vincent
> > Inviato: mercoledì 10 ottobre 2007 15.42
> > A: Emmanuel Lacour
> > Cc: rt-users at lists.bestpractical.com
> > Oggetto: Re: [rt-users] Chart grouped by customfield
> > 
> > 
> > On Oct 10, 2007, at 9:38 AM, Emmanuel Lacour wrote:
> > 
> > > On Wed, Oct 10, 2007 at 09:09:58AM -0400, Jesse Vincent wrote:
> > >>
> > >
> > > 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?
> > 
> > > 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 :)
> > >
> > > _______________________________________________
> > > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> > >
> > > Community help: http://wiki.bestpractical.com Commercial support: 
> > > sales at bestpractical.com
> > >
> > >
> > > Discover RT's hidden secrets with RT Essentials from 
> O'Reilly Media.
> > > Buy a copy at http://rtbook.bestpractical.com
> > >
> > 
> > 
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com Commercial 
> support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com
>