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