I extracted query which RT sends to database server. Query " 'CF.{Approval}' LIKE '1. Pending' " which is in RT current search field.
2008/3/5, Kenneth Crocker <[EMAIL PROTECTED]>: > > Arkadiusz, > > > Is this query you are writing in RT/Ticket SQL or native SQL? I > use RT > query with custom fields all the time for reporting and have no problem > at all. I also use native SQL for other queries on our Oracle DataBase. > Using native SQL against the DataBase, however, requires some finesse > when trying to get certain data. For example, to get the value of a CF > that is applied to tickets in a queue I could use the Ticket ID to go to > the OBJECTCUSTOMFIELDVALUES, make sure it is a ticket CF and not > disabled and use the CONTENT from OBJECTCUSTOMFIELDVALUES with any other > Ticket data for my report. I don't see a need for all those joins. But > hey, that's just me. Hope this helps. > > Kenn > LBNL > > > On 3/5/2008 12:45 AM, Arkadiusz Jakubas wrote: > > It this some kind of bug ? > > > > > > 2008/2/14, Arkadiusz Jakubas <[EMAIL PROTECTED] > > > <mailto:[EMAIL PROTECTED]>>: > > > > > I extracted sql query ( 'CF.{Approval}' LIKE '1. Pending' ) : > > > > > SELECT COUNT(DISTINCT main.id <http://main.id>) FROM (((Tickets > > > main LEFT JOIN ObjectCustomFields ObjectCustomFields_1 ON > > ((ObjectCustomFields_1.ObjectId = '0')) AND( > > 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 <http://main.id>)) > > > AND( ObjectCustomFieldValues_3.CustomField = CustomFields_2.id) > > AND( (ObjectCustomFieldValues_3.Disabled = '0')) AND( > > (ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket'))) WHERE > > ((CustomFields_2.Name = 'Approval')) AND ((main.EffectiveId = > > > main.id <http://main.id>)) AND ((main.Status != 'deleted')) AND > > > ((main.Type = 'ticket')) AND ( ( (ObjectCustomFieldValues_3.Content > > LIKE '%1. Pending%') ) ) > > > > result : > > +-------------------------+ > > > | COUNT(DISTINCT main.id <http://main.id>) | > > > +-------------------------+ > > | 0 | > > +-------------------------+ > > > > Then i modified query a little removed: > > (ObjectCustomFieldValues_3.Content LIKE '%1. Pending%') > > and > > (CustomFields_2.Name = 'Approval')) > > > > changed from: > > > SELECT COUNT(DISTINCT main.id <http://main.id>) > > > to > > SELECT * > > > > query: > > SELECT * FROM (((Tickets main LEFT JOIN ObjectCustomFields > > ObjectCustomFields_1 ON ((ObjectCustomFields_1.ObjectId = '0')) > > AND( 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 <http://main.id>)) > > > AND( ObjectCustomFieldValues_3.CustomField = CustomFields_2.id) > > AND( (ObjectCustomFieldValues_3.Disabled = '0')) AND( > > (ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket'))) WHERE > > > ((main.EffectiveId = main.id <http://main.id>)) AND ((main.Status != > > > 'deleted')) AND ((main.Type = 'ticket')) order by main.LastUpdated > > desc limit 100 ; > > > > > > some result: > > > > | id | EffectiveId | Queue | Type | IssueStatement | Resolution > > | Owner | Subject | InitialPriority | > > FinalPriority | Priority | TimeEstimated | TimeWorked | Status | > > TimeLeft | Told | Starts | > > Started | Due | Resolved | > > LastUpdatedBy | LastUpdated | Creator | Created > > | Disabled | id | CustomField | ObjectId | SortOrder | Creator | > > Created | LastUpdatedBy | LastUpdated | id | Name | Type | > > Description | SortOrder | Creator | Created | LastUpdatedBy | > > LastUpdated | Disabled | LookupType | Repeated | Pattern | MaxValues > > | id | ObjectId | CustomField | Content | Creator | Created | > > LastUpdatedBy | LastUpdated | ObjectType | LargeContent | > > ContentType | ContentEncoding | SortOrder | Disabled | > > > +-------+-------------+-------+--------+----------------+------------+-------+---------------------------+-----------------+---------------+----------+---------------+------------+--------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------+---------------------+---------+---------------------+----------+------+-------------+----------+-----------+---------+---------+---------------+-------------+------+------+------+-------------+-----------+---------+---------+---------------+-------------+----------+------------+----------+---------+-----------+------+----------+-------------+---------+---------+---------+---------------+-------------+------------+--------------+-------------+-----------------+-----------+----------+ > > | 22285 | 22285 | 6 | ticket | 0 | 0 > > | 91191 | Juniper | 20 | 39 | 22 > > | 0 | 0 | open | 0 | 2008-02-14 > > 08:24:01 | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 2008-02-24 > > 01:13:50 | 1970-01-01 00:00:00 | 620 | 2008-02-14 08:24:01 > > | 50067 | 2008-02-13 20:18:20 | 0 | NULL | NULL > > | NULL | NULL | NULL | NULL | NULL | > > NULL | NULL | NULL | NULL | NULL | NULL | NULL > > | NULL | NULL | NULL | NULL | NULL > > | NULL | NULL | NULL | NULL | NULL | NULL | > > NULL | NULL | NULL | NULL | NULL | > > NULL | NULL | NULL | NULL | > > NULL | NULL | > > | 22269 | 22269 | 53 | ticket | 0 | 0 > > | 93603 | Account | 10 | 29 | 19 > > | 0 | 30 | open | 0 | 2008-02-13 > > 10:04:11 | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 2008-02-17 > > 16:01:13 | 1970-01-01 00:00:00 | 5786 | 2008-02-14 07:00:43 > > | 93260 | 2008-02-12 16:01:13 | 0 | NULL | NULL > > | NULL | NULL | NULL | NULL | NULL | > > NULL | NULL | NULL | NULL | NULL | NULL | NULL > > | NULL | NULL | NULL | NULL | NULL > > | NULL | NULL | NULL | NULL | NULL | NULL | > > NULL | NULL | NULL | NULL | NULL | > > NULL | NULL | NULL | NULL | > > NULL | NULL | > > | 22286 | 22286 | 47 | ticket | 0 | 0 > > | 50067 | Server reboot | 10 | > > 29 | 14 | 0 | 60 | open | 0 | > > 2008-02-14 04:13:11 | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | > > 2008-02-19 02:50:52 | 1970-01-01 00:00:00 | 5786 | > > 2008-02-14 07:00:30 | 96040 | 2008-02-14 02:50:52 | 0 | > > NULL | NULL | NULL | NULL | NULL | NULL > > | NULL | NULL | NULL | NULL | NULL | NULL > > | NULL | NULL | NULL | NULL | NULL | > > NULL | NULL | NULL | NULL | NULL | NULL | NULL > > | NULL | NULL | NULL | NULL | NULL | > > NULL | NULL | NULL | NULL | > > NULL | NULL | NULL | > > > > > > Is this some kind of bug ? There shouldn't be so many NULLs > > > > > > > > > > -- > > Arkadiusz Jakubas > > Arces Network, LLC > > http://www.arces.net > > > > > > > ------------------------------------------------------------------------ > > > > _______________________________________________ > > 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 > > -- Arkadiusz Jakubas Arces Network, LLC http://www.arces.net
_______________________________________________ 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