[Rt-devel] [Fwd: Command line query involving Custom Fields]

[Rangarajan Radhakrishnan]

Rangarajan Radhakrishnan wrote:

> I have some custom field that I am trying to query as follows:
> rt list "Queue = 'TestQueue' and owner = 'nobody' AND ('CF.{My 
> Field1}' = 'My Value1')
> (This works).
>
> However, when I add another condition:
> rt list "Queue = 'TestQueue' and owner = 'nobody' AND ('CF.{My 
> Field1}' = 'My Value1' OR 'CF.{My Field1}' = 'My Value2')
>
> It never returns any row. This has been tried from the graphical 
> interface too.
>
> Details:
> Using RT 3.6.0pre0. Using 1.43 for DBIx::SearchBuilder. Have tried it 
> with 1.39 of SearchBuilder.pm too.
> Database is Oracle 10g (10.1)
>
> Actual query used with "rt list" command is given below:
> -------------------------------------
> Queue = 'Assay Development'  AND  ('CF.{Assay Stage}' = 'Assay 
> Request' OR 'CF.{Assay Stage}' = 'Feasibility' OR 'CF.{Assay Stage}' = 
> 'Assay Development' OR 'CF.{Assay Stage}' = 'Assay Validation' OR 
> 'CF.{Assay Stage}' = 'Sample Analysis' OR 'CF.{Assay Stage}' = 'Data 
> Loading' )
> -------------------------------------
> It seems to breaks down even if I just use two clauses involving same 
> customfield within parenthesis.
>
> This results in QueryString (Select) as printed from within 
> DBIx::SearchBuilder is:
>
>
> SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM ( SELECT 
> main.* FROM ( SELECT DISTINCT main.id FROM (((Tickets main  LEFT JOIN 
> ObjectCustomFields ObjectCustomFields_1  ON 
> ((ObjectCustomFields_1.ObjectId = '0')) OR (  
> ObjectCustomFields_1.ObjectId = main.Queue))  LEFT JOIN CustomFields 
> CustomFields_2  ON ( CustomFields_2.id = 
> ObjectCustomFields_1.CustomField))  LEFT JOIN ObjectCustomFieldValues 
> ObjectCustomFieldValues_3  ON ((ObjectCustomFieldValues_3.ObjectId = 
> main.id)) AND (  ObjectCustomFieldValues_3.CustomField = 
> CustomFields_2.id) AND ( (ObjectCustomFieldValues_3.Disabled = '0')) 
> AND ( (ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket')))   WHERE 
> ((CustomFields_2.name = 'Assay Stage')) AND ((main.EffectiveId = 
> main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 
> 'ticket')) AND ((main.name = 'Assay Stage')AND(main.name = 'Assay 
> Stage')AND(main.name = 'Assay Stage')AND(main.name = 'Assay 
> Stage')AND(main.name = 'Assay Stage')) AND ((main.Queue = '3')AND (  ( 
> (ObjectCustomFieldValues_3.Content = 'Assay Request') ) OR ( 
> (ObjectCustomFieldValues_3.Content = 'Feasibility') ) OR ( 
> (ObjectCustomFieldValues_3.Content = 'Assay Development') ) OR ( 
> (ObjectCustomFieldValues_3.Content = 'Assay Validation') ) OR ( 
> (ObjectCustomFieldValues_3.Content = 'Sample Analysis') ) OR ( 
> (ObjectCustomFieldValues_3.Content = 'Data Loading') )  ) )  ) 
> distinctquery, Tickets main WHERE (main.id = distinctquery.id)  ORDER 
> BY main.id ASC  ) limitquery WHERE rownum <= 50 ) WHERE limitrownum >= 1
>
> On running this query using SQLPLUS
> ERROR at line 1:
> ORA-00904: "MAIN"."NAME": invalid identifier
>
> Has anybody seen this before? Thanks in advance for your help.
>
>
>
It appears that the generated query has
"(main.name = 'Assay Stage')AND(main.name = 'Assay Stage')AND(main.name 
= 'Assay Stage')AND(main.name = 'Assay Stage')AND(main.name = 'Assay 
Stage')"
repeating multiple times.

each "main.name" should instead be "CustomFields_2.name"

Is anybody else facing this problem ....? Thanks.
-------------- next part --------------
An embedded message was scrubbed...
From: Rangarajan Radhakrishnan <rangarajan.radhakrishnan at bms.com>
Subject: Command line query involving Custom Fields
Date: Mon, 01 May 2006 15:53:56 -0400
Size: 3408
Url: http://lists.bestpractical.com/pipermail/rt-devel/attachments/20060501/86886a08/CommandlinequeryinvolvingCustomFields.eml