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 _______________________________________________ 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