I extracted sql query ( 'CF.{Approval}' LIKE '1. Pending' ) :
SELECT COUNT(DISTINCT 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))
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)) AND
((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( (
(ObjectCustomFieldValues_3.Content LIKE '%1. Pending%') ) )
result :
+-------------------------+
| COUNT(DISTINCT 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)
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)) AND(
ObjectCustomFieldValues_3.CustomField = CustomFields_2.id) AND(
(ObjectCustomFieldValues_3.Disabled = '0')) AND(
(ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket'))) WHERE ((
main.EffectiveId = 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
_______________________________________________
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
