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.
--- Begin Message ---
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.
--- End Message ---
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
We're hiring! Come hack Perl for Best Practical:
http://bestpractical.com/about/jobs.html
