[rt-users] [Fwd: Command line query involving Custom Fields]
Rangarajan Radhakrishnan
rangarajan.radhakrishnan at bms.com
Mon May 1 16:36:39 EDT 2006
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-users/attachments/20060501/86886a08/attachment.eml>
More information about the rt-users
mailing list