On 2/13/2007 3:14 AM, Joop wrote:
Could you post the TicketSQL for your search?
You can get this from the QueryBuilder page and than 'Advanced'
With this data:
CF1 = Select one text field (One, Two, Three, Four, Five)
CF2 = numeric
# CF1 CF2
=== ====== =====
1 Four 0.1
2 Two 0.1
3 Five 0.1
21 Two 23
22 One 23
41 Three 3
42 Five 0
55 Two 0.5
56 Two 0
Running this query:
SELECT id
FROM
(SELECT limitquery.*,rownum limitrownum
FROM
(SELECT main.*
FROM
(SELECT 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')))
LEFT JOIN CustomFieldValues CustomFieldValues_4
ON ((CustomFieldValues_4.Name =
ObjectCustomFieldValues_3.Content))
AND (CustomFieldValues_4.CustomField =
ObjectCustomFieldValues_3.CustomField))
WHERE ((main.EffectiveId = main.id))
AND ((main.Status != 'deleted'))
AND ((main.Type = 'ticket'))
AND (((main.Owner = '86'))
AND ((main.Status = 'new')
OR (main.Status = 'open')))
GROUP BY main.id
ORDER BY min(CustomFieldValues_4.SortOrder) DESC,
min(ObjectCustomFieldValues_3.Content) DESC)
distinctquery,
Tickets main
WHERE (main.id = distinctquery.id)) limitquery
WHERE rownum <= 10)
WHERE limitrownum >= 1;
Results in:
# CF1 CF2
=== ====== =====
3 Five 0.1
42 Five 0
1 Four 0.1
41 Three 3
21 Two 23
55 Two 0.5
2 Two 0.1
56 Two 0
22 One 23
I confirmed that the same query run in SQL*Plus returns the same row
order. Is this the "Ticket SQL" you were looking for:
Owner = '86' AND ( Status = 'new' OR Status = 'open')
Thanks for the help!
--
Regards,
joe
Joe Casadonte
[EMAIL PROTECTED]
_______________________________________________
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
